Hi, I'm trying to figure out why an a composite INDEX is only being partially used during a query Given the following tables, MariaDB [tmp]> 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) MariaDB [tmp]> 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) The following query seems to be only using the refid part of the index MariaDB [tmp]> 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 | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+----------+------+-------------------+-----------+---------+-----------------+------+-----------------------+ | 1 | SIMPLE | reftable | ref | PRIMARY,serverIdx | serverIdx | 2 | const | 4 | | | 1 | SIMPLE | valtable | ref | refid | refid | 3 | tmp.reftable.id | 690 | Using index condition | +------+-------------+----------+------+-------------------+-----------+---------+-----------------+------+-----------------------+ But if I change the query and and directly specify the refids, it makes full use of the index MariaDB [tmp]> SELECT id FROM reftable WHERE groupid = 2000; +-------+ | id | +-------+ | 9996 | | 9997 | | 9998 | | 9999 | | 10000 | +-------+ 5 rows in set (0.00 sec) MariaDB [tmp]> 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 | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+----------+-------+---------------+------------+---------+------+------+-----------------------+ | 1 | SIMPLE | valtable | range | refTimeIdx | refTimeIdx | 11 | NULL | 26 | Using index condition | +------+-------------+----------+-------+---------------+------------+---------+------+------+-----------------------+ 1 row in set (0.00 sec) Is this expected behaviour and if so is there any reference that explains the behaviour? Thanks, Conor