Composite index usage during join
Hi,
I'm trying to figure out why an a composite INDEX is only being partially used during a query. Given the following tables
mysql> SHOW CREATE TABLE reftable;
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| reftable | CREATE TABLE `reftable` (
`id` mediumint(9) NOT NULL AUTO_INCREMENT,
`groupid` smallint(6) NOT NULL,
`name` varchar(50) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL,
PRIMARY KEY (`id`),
KEY `groupIdIdx` (`groupid`)
) ENGINE=MyISAM AUTO_INCREMENT=25001 DEFAULT CHARSET=latin1 |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE valtable;
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| valtable | CREATE TABLE `valtable` (
`refid` mediumint(9) NOT NULL,
`time` datetime NOT NULL,
`value` int(10) unsigned NOT NULL,
KEY `refTimeIdx` (`refid`,`time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
The following query seems to be only using the refid part of the index
mysql> EXPLAIN SELECT AVG(valtable.value) FROM valtable, reftable WHERE valtable.refid = reftable.id AND reftable.groupid = 2000 AND valtable.time BETWEEN '2018-01-15 00:00:00' AND '2018-01-15 23:59:59';
+----+-------------+----------+------------+------+--------------------+------------+---------+-----------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+--------------------+------------+---------+-----------------+------+----------+-----------------------+
| 1 | SIMPLE | reftable | NULL | ref | PRIMARY,groupIdIdx | groupIdIdx | 2 | const | 4 | 100.00 | NULL |
| 1 | SIMPLE | valtable | NULL | ref | refTimeIdx | refTimeIdx | 3 | tmp.reftable.id | 690 | 11.11 | Using index condition |
+----+-------------+----------+------------+------+--------------------+------------+---------+-----------------+------+----------+-----------------------+
2 rows in set, 1 warning (0.00 sec)
mysql>
But if I change the query and and directly specify the refids, it makes full use of the index
mysql> EXPLAIN
-> SELECT AVG(valtable.value)
-> FROM valtable
-> WHERE
-> valtable.refid IN ( 9996, 9997, 9998, 9999, 10000 ) AND
-> valtable.time BETWEEN '2018-01-15 00:00:00' AND '2018-01-15 23:59:59';
+----+-------------+----------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | valtable | NULL | range | refTimeIdx | refTimeIdx | 8 | NULL | 26 | 100.00 | Using index condition |
+----+-------------+----------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql>
Is this expected behaviour and if so is there any reference that explains the behaviour?
Thanks,
Conor