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