I'm using MySQL 5.6 and my storage engine is InnoDB.
I have a table with 1 million rows containing with the columns:
- ID (primary key)
- FirstName
- LastName
- foreign_key_id (foreign key, NOT NULL)
- foreign_key_id2 (another foreign key, default NULL)
The rows are seperated under:
- 25% with foreign_key_id value 1 and foreign_key_id2 NULL
- 25% with foreign_key_id value 1 and foreign_key_id2 NOT NULL
- 25% with foreign_key_id value 2 and foreign_key_id2 NULL
- 25% with foreign_key_id value 2 and foreign_key_id2 NOT NULL
With the following indexes:
- index foreign_key_idx on foreign_key_id
- index foreign_key_2_idx on and foreign_key_id2
- composite index foreign_key_comp_idx on (foreign_key_idx, foreign_key_2_idx)
I perform the following queries:
Query 1 - without indexes:
SELECT *
FROM table tbl
IGNORE INDEX(foreign_key_idx, foreign_key_2_idx, foreign_key_comp_idx)
WHERE tbl.foreign_key_id = 1 AND tbl.foreign_key_id2 IS NOT NULL
Query 2 - with indexes (no composite index):
SELECT *
FROM table tbl
IGNORE INDEX(foreign_key_comp_idx)
WHERE tbl.foreign_key_id = 1 AND tbl.foreign_key_id2 IS NOT NULL
Query 3 - with composite index (no other indexes):
SELECT *
FROM table tbl
IGNORE INDEX(foreign_key_idx, foreign_key_2_idx)
WHERE tbl.foreign_key_id = 1 AND tbl.foreign_key_id2 IS NOT NULL
The results:
Query 1 (no indexes) performs a full table scan and uses 1 million records with a total duration of 0.37 seconds.
Query 2 (indexes, no composite index) performs a non-unique key lookup on foreign_key_idx index and uses 500K records with a total duration of 0.6 seconds.
Query 3 (composite index only) performs an index range scan on composite index and uses 480K records with a total duration of 0.13 seconds.
What I really don't understand is: why is query 2 (with indexes) always performing slower than query 1 (without indexes)? I'm really really stuck and need some help...
I've tested the queries above with different amount of rows, like 1k, 10k, 20k, 50k, 100k, 200k, 250k, 500k, 1M etc, always with the same ratio (25%), and the results where the same (query 2 always performing slow)
Thank you in advance, really appreciate any kind of input!
Edit (2 May 2016)
SHOW CREATE TABLE COMMAND:
CREATE TABLE `table` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`FirstName` varchar(255) NOT NULL,
`LastName` varchar(255) NOT NULL,
`foreign_key_id` int(11) NOT NULL,
`foreign_key_id2` int(11) DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `foreign_key_idx` (`foreign_key_id`),
KEY `foreign_key_2_idx` (`foreign_key_id2`),
KEY `foreign_key_comp_idx ` (`foreign_key_id`,`foreign_key_id2`),
CONSTRAINT `foreign_key_idx` FOREIGN KEY (`foreign_key_id`) REFERENCES `table2` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `foreign_key_2_idx` FOREIGN KEY (`foreign_key_id2`) REFERENCES `table3` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
) ENGINE=InnoDB AUTO_INCREMENT=1515998 DEFAULT CHARSET=latin1
Not sure if important, but table2 has 20 records and table3 also 1 million.
Extra
column for example can show some optimizations being used)