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 protected]> 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 protected]> 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 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/jtayler%40oeinc.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/dleber_wodev%40codeferous.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/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/archive%40mail-archive.com This email sent to [email protected]
