Re: EOQualifier proper fetch across to-many?
Re: EOQualifier proper fetch across to-many?
- Subject: Re: EOQualifier proper fetch across to-many?
- From: Jesse Tayler <email@hidden>
- Date: Tue, 06 Mar 2012 12:57:03 -0500
why would I have to dump my data just to create the indexes?
should I not be able to create indexes on the fly on mysql?
and yes, this is my first mysql setup and no, I did NOT realize fully that I had to create indexes - some have been, other not, it seems...
anyone have a nice - simple WO-person's explanation of indexing strategy? especially needs for things like a data dump/restore or these attributed correlations spanning multiple entities and supporting/troubleshooting queries?
On Mar 6, 2012, at 12:00 PM, Theodore Petrosky <email@hidden> wrote:
> This conversation has piqued my interest.
> I just looked at my postgresql database to see what indexes are created in a 'normal' migration and I was happy to see that the foreign key did get an index:
>
> Indexes:
> "person_pk" PRIMARY KEY, btree (id)
> "person_erattachmentid_idx" btree (erattachmentid)
> Foreign-key constraints:
> "person_erattachmentid_id_fk" FOREIGN KEY (erattachmentid) REFERENCES erattachment(id) DEFERRABLE INITIALLY DEFERRED
>
> inquiring minds need to know
>
>
>> ------------------------------
>>
>> Message: 6
>> Date: Tue, 06 Mar 2012 11:16:55 -0500
>> From: Kieran Kelleher <email@hidden>
>> To: Jesse Tayler <email@hidden>
>> Cc: WebObjects Development <email@hidden>
>> Subject: Re: EOQualifier proper fetch across to-many?
>> Message-ID: <email@hidden>
>> Content-Type: text/plain; charset="utf-8"
>>
>> Whoa..... yes, YOU MUST create foreign key indexes yourself
>> in MySQL! (The auto SQL from EntityModeler does not do it
>> for you since creating true foreign key constraints in MySQL
>> is a rat's nest of problems due to the lack of the most
>> desired feature that MySQL lacks currently (deferred
>> constraints)
>>
>> Dump a schema (mysqldump --no-data > schema.sql) of your
>> db and highlight all FKs that need indexes and create them
>> asap ..... your performance on relationships will soar on
>> larger tables.
>>
>> As a rule, I create FK indexes on every table - would not
>> give it a second thought not to create them.
>>
>> Also, on the many-to-many relationship "join table", the
>> default SQL will have created the compound PK using the two
>> FK fields, however you should also create a INDEX with the
>> two same keys in the opposite order..... for example, if
>> your join table has two fields A and B, then the compound PK
>> might be (A,B) in which case you need to add another index
>> based on (B,A)
>>
>> HTH, Kieran
>>
>>
>> On Mar 6, 2012, at 11:03 AM, Jesse Tayler wrote:
>>
>>> oh, the fetch kills the database alright -- I'll
>> attempt to fix with indexes, but I've had mixed luck with
>> that.
>>>
>>> I notice there's not all the indexes I'd expect on
>> foreign keys? mysql have anything funny there? or I should
>> have at least an index for each foreign key, no?
>>>
>>>
>>>
>>> On Mar 6, 2012, at 8:48 AM, Kieran Kelleher <email@hidden>
>> wrote:
>>>
>>>> Prematurely looking for a fetch solution that does
>> not overkill the database when the we don't know if the
>> fetch overkills the database yet. :-)
>>>>
>>>> Regards Kieran
>>>> ___________________________
>>>> Sent from my iPad.
>>>>
>>>>
>>>> On Mar 5, 2012, at 9:44 PM, Paul Yu <email@hidden>
>> wrote:
>>>>
>>>>> Premature what?
>>>>>
>>>>> --
>>>>> Paul Yu
>>>>> Sent with Sparrow
>>>>>
>>>>> On Monday, March 5, 2012 at 8:55 PM, Kieran
>> Kelleher wrote:
>>>>>
>>>>>> Donald Knuth once said "premature
>> optimization is the root of all evil" :-)
>>>>>>
>>>>>> Try it out before assuming the performance
>> is bad. If your tables have the needed indexes it should be
>> fine.
>>>>>>
>>>>>> If performance is bad, log the generated
>> SQL and just apply whatever tools you have at your disposal
>> for your database platform to figure out the problem (index,
>> join buffer size, etc.)
>>>>>>
>>>>>> Regards Kieran
>>>>>> ___________________________
>>>>>> Sent from my iPad.
>>>>>>
>>>>>>
>>>>>> On Mar 5, 2012, at 3:43 PM, Jesse Tayler
>> <email@hidden>
>> wrote:
>>>>>>
>>>>>>>
>>>>>>> is there a proper way to fetch across a
>> to-many and not overkill the database?
>>>>>>>
>>>>>>> if I wanted to return a list of
>> recently used venues that the user has associated with posts
>> they have authored, I'd want a distinct return of venues,
>> each having a post->author being the user, but this query
>> like this would just churn on the database wouldn't it?
>>>>>>>
>>>>>>> I didn't see a "distinct" wonder fetch
>> property either, don't I have to use something to ensure the
>> list is returned without duplicates?
>>>>>>>
>>>>>>> EOQualifier qual =
>> Venue.POSTS.dot(Post.AUTHOR_KEY).eq(user());
>>>>>>> ERXRestFetchSpecification<Venue>
>> fetchSpec = new
>> ERXRestFetchSpecification<Venue>(Venue.ENTITY_NAME,
>> qual, null, queryFilter(), Venue.CREATED.descs(), 25);
>>>>>>>
>>>>>>> what's the best practice on that kind
>> of fetch?
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>> _______________________________________________
>>>>>>> Do not post admin requests to the list.
>> They will be ignored.
>>>>>>> Webobjects-dev mailing list (email@hidden)
>>>>>>> Help/Unsubscribe/Update your
>
>
> _______________________________________________
> Do not post admin requests to the list. They will be ignored.
> Webobjects-dev mailing list (email@hidden)
> Help/Unsubscribe/Update your Subscription:
>
> This email sent to email@hidden
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden