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:
Code:
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:
Code:
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:
Code:
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:
Code:
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