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