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 Subscription: >>>>> https://lists.apple.com/mailman/options/webobjects-dev/kelleherk%40gmail.com >>>>> >>>>> This email sent to [email protected] >>>> >>>> _______________________________________________ >>>> 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/pyu%40mac.com >>>> >>>> This email sent to [email protected] >>> >
_______________________________________________ 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]
