Some examples: many to many join table ------------------------------
CREATE TABLE `xxprogramcsagency` ( `oidprogram` int(11) NOT NULL, `oidagency` int(11) NOT NULL, PRIMARY KEY (`oidprogram`,`oidagency`), KEY `idx_agency_program` (`oidagency`,`oidprogram`) ) ENGINE=InnoDB; regular table with FL index ------------------------------------ CREATE TABLE `statement` ( `closingbalance` decimal(18,7) DEFAULT NULL, `closingdate` datetime DEFAULT NULL, `closingtargetbalance` decimal(18,7) DEFAULT NULL, `oid` int(11) NOT NULL, `openingbalance` decimal(18,7) NOT NULL, `oidaccount` int(11) NOT NULL, `openingdate` datetime DEFAULT NULL, PRIMARY KEY (`oid`), KEY `fk_oidaccount` (`oidaccount`) ) ENGINE=InnoDB; On Mar 6, 2012, at 11:16 AM, Kieran Kelleher wrote: > 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]
