| 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@hidden> 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 ___________________________
Premature what?
On Monday, March 5, 2012 at 8:55 PM, Kieran Kelleher wrote:
|