mysql> select count
from hosts;
----------
| count |
----------
| 18294 |
----------
1 row in set (0.00 sec)
mysql> select count from groups;
----------
| count |
----------
| 26 |
----------
1 row in set (0.00 sec)
mysql> select count from hosts_groups;
----------
| count |
----------
| 54822 |
----------
1 row in set (0.01 sec)
mysql> explain SELECT DISTINCT h.hostid,h.name,hg.groupid FROM hosts h,hosts_groups hg USE INDEX ( hosts_groups_2) WHERE (hg.groupid IN ('148','25','24','21','147','11','9','26','29','28','14','19','12','18','13','10','16','15','20','17','22','4')) AND hg.hostid=h.hostid AND h.status=0 AND h.hostid BETWEEN 000000000000000 AND 099999999999999;
--------------------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
--------------------------------------------------------------------------------------------------------+
| 1 | SIMPLE | hg | ALL | hosts_groups_2 | NULL | NULL | NULL | 55669 | Using where; Using temporary |
| 1 | SIMPLE | h | eq_ref | PRIMARY,hosts_2 | PRIMARY | 8 | zabbix.hg.hostid | 1 | Using where |
--------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
This query executes ~0.5seconds. This query does not use indexes for "hosts_groups" table.
mysql> explain SELECT DISTINCT h.hostid,h.name,hg.groupid FROM hosts h,hosts_groups hg WHERE (hg.groupid IN ('148','25','24','21','147','11','9','26','29','28','14','19','12','18','13','10','16','15','20','17','22','4')) AND hg.hostid=h.hostid AND h.status=0 AND h.hostid BETWEEN 000000000000000 AND 099999999999999;
--------------------------------------------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
--------------------------------------------------------------------------------------------------------------------------------+
| 1 | SIMPLE | h | ref | PRIMARY,hosts_2 | hosts_2 | 4 | const | 8979 | Using where; Using temporary |
| 1 | SIMPLE | hg | range | hosts_groups_1,hosts_groups_2 | hosts_groups_1 | 16 | NULL | 27834 | Using where; Using index; Using join buffer |
--------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
The new query executes ~142seconds.
Mysql query optimizer uses "hosts_groups_1". But query selects from "hosts" table almost all of data. So this query executes much longer.