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 protected]>
> To: Jesse Tayler <[email protected]>
> Cc: WebObjects Development <[email protected]>
> Subject: Re: EOQualifier proper fetch across to-many?
> Message-ID: <[email protected]>
> 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 protected]>
> 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 protected]>
> 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 protected]>
> 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 protected])
> >>>>> Help/Unsubscribe/Update your
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list ([email protected])
Help/Unsubscribe/Update your Subscription:
https://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com
This email sent to [email protected]
