I have a very strange case, where a index for a foreign key dont seem to work.
The two columns in the invoice table “matrix_log_id” and “commission_log_id” actually link to the matrix_log table and the “matrix_log_id” column there, they just refer to two different case types (invoice, commission payout).
The foreign key restrictions work as they should, but it seems the index of the column does not work properly. When I do a EXPLAIN on the query below, it says that it use the “fk_invoice_matrix_log2” index, and that it search through 40299 rows (The index on matrix_log_id works as it should).
If I now add an index on the column (ALTER TABLE invoice ADD INDEX test (commission_log_id)
the result is that it use the new index (test) and it match at once. (Edit: I was assuming that it used the new index, when looking I see that it still chose the foreign key index which makes sense as its cardinality is higher, but it does not explain why it goes from looking through 40k rows to match at once after the new index is added).
What I am wondering on here, is if I have misunderstood something regarding indexes that also have a foreign key constraint. As I have always believed that you should never have two indexes on the same column (i.e. for one column, not considering if you index multiple columns into one index).
If anyone can share some insight on what is happening here it would be great.
Thanks
Note. The table code, query and explains are below.
The table in question:
CREATE TABLE IF NOT EXISTS `invoice` (
`invoice_id` INT NOT NULL AUTO_INCREMENT ,
`matrix_log_id` INT NULL ,
`commission_log_id` INT NULL ,
PRIMARY KEY (`invoice_id`) ,
INDEX `fk_invoice_matrix_log1` (`matrix_log_id` ASC) ,
INDEX `fk_invoice_matrix_log2` (`commission_log_id` ASC) ,
CONSTRAINT `fk_invoice_matrix_log1`
FOREIGN KEY (`matrix_log_id` )
REFERENCES `matrix_log` (`matrix_log_id` )
ON DELETE RESTRICT
ON UPDATE CASCADE,
CONSTRAINT `fk_invoice_matrix_log2`
FOREIGN KEY (`commission_log_id` )
REFERENCES `matrix_log` (`matrix_log_id` )
ON DELETE RESTRICT
ON UPDATE CASCADE)
ENGINE = InnoDB
The query in question:
SELECT
[removed as it has no relevance]
FROM
matrix_log as log
LEFT JOIN matrix_log_detailed as detailed
USING (matrix_log_id)
LEFT JOIN matrix_log_funding as fund
ON (log.matrix_log_id=fund.matrix_log_id)
LEFT JOIN tosb_info as tosb
ON (log.matrix_log_id=tosb.matrix_log_id)
LEFT JOIN invoice
ON (log.matrix_log_id=invoice.matrix_log_id)
LEFT JOIN invoice as comm
ON (log.matrix_log_id=comm.commission_log_id)
INNER JOIN matrix_import as imp
ON (log.matrix_user_id=imp.matrix_user_id)
WHERE
log.matrix_user_id=:user_id
ORDER BY
log.matrix_log_date DESC
This is the first explain:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE log ref fk_matrix_log_matrix_user1 fk_matrix_log_matrix_user1 4 const 5 Using where; Using filesort
1 SIMPLE imp ref fk_matrix_import_matrix_user1 fk_matrix_import_matrix_user1 5 const 1 Using where
1 SIMPLE detailed ref fk_matrix_log_detailed_matrix_log1 fk_matrix_log_detailed_matrix_log1 4 lgn.log.matrix_log_id 1
1 SIMPLE fund ref fk_matrix_funding_matrix_log1 fk_matrix_funding_matrix_log1 4 lgn.log.matrix_log_id 1
1 SIMPLE tosb ref matrix matrix 5 lgn.log.matrix_log_id 1
1 SIMPLE invoice ref fk_invoice_matrix_log1 fk_invoice_matrix_log1 5 lgn.log.matrix_log_id 1 Using index
1 SIMPLE comm ref fk_invoice_matrix_log2 fk_invoice_matrix_log2 5 lgn.log.matrix_log_id 40299 Using index
This is the second explain:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE log ref fk_matrix_log_matrix_user1 fk_matrix_log_matrix_user1 4 const 5 Using where; Using filesort
1 SIMPLE imp ref fk_matrix_import_matrix_user1 fk_matrix_import_matrix_user1 5 const 1 Using where
1 SIMPLE detailed ref fk_matrix_log_detailed_matrix_log1 fk_matrix_log_detailed_matrix_log1 4 lgn.log.matrix_log_id 1
1 SIMPLE fund ref fk_matrix_funding_matrix_log1 fk_matrix_funding_matrix_log1 4 lgn.log.matrix_log_id 1
1 SIMPLE tosb ref matrix matrix 5 lgn.log.matrix_log_id 1
1 SIMPLE invoice ref fk_invoice_matrix_log1 fk_invoice_matrix_log1 5 lgn.log.matrix_log_id 1 Using index
1 SIMPLE comm ref fk_invoice_matrix_log2,test fk_invoice_matrix_log2 5 lgn.log.matrix_log_id 1 Using index