Re: EOQualifier proper fetch across to-many?
Re: EOQualifier proper fetch across to-many?
- Subject: Re: EOQualifier proper fetch across to-many?
- From: David LeBer <email@hidden>
- Date: Tue, 06 Mar 2012 13:34:54 -0500
Jesse,
I don't think anyone said anything about dumping your data.
Kieran said dump your 'schema' not data.
--
David LeBer
Codeferous Software
On 2012-03-06, at 12:57 PM, Jesse Tayler wrote:
> 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
_______________________________________________
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