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)

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