Re: EOQualifier proper fetch across to-many?
Re: EOQualifier proper fetch across to-many?
- Subject: Re: EOQualifier proper fetch across to-many?
- From: Kieran Kelleher <email@hidden>
- Date: Tue, 06 Mar 2012 14:54:28 -0500
Hi Jesse
LOL I just meant for easy inspection in BBEdit (or even a printout) to identify (or highlight on paper) all FKs that need a Index! :-)
Regards, Kieran.
(Sent from my iPhone)
On Mar 6, 2012, at 1:34 PM, David LeBer <email@hidden> wrote:
> 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
_______________________________________________
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