slow queries on history_log

[[email protected]]

Hello,

When looking at a log of mysql slow queries, I noticed the a lot of slow queries on history_log.
We are using mysql 5.0.51 and zabbix 8.0.
Maybe an index should be added ?


select value from history_log where itemid=42531 order by id desc limit 1;
# Time: 100410 19:10:43
# User@Host: zabbix[zabbix] @ svr-zabbix.kbcadam.intra [10.85.207.60]
# Query_time: 33 Lock_time: 0 Rows_sent: 1 Rows_examined: 234912
select value from history_log where itemid=26102 order by id desc limit 1;
# Time: 100410 19:10:46
# User@Host: zabbix[zabbix] @ svr-zabbix.kbcadam.intra [10.85.207.60]
# Query_time: 2 Lock_time: 0 Rows_sent: 1 Rows_examined: 1801
select value from history_log where itemid=26331 order by id desc limit 1;
# Time: 100410 19:11:04
# User@Host: zabbix[zabbix] @ svr-zabbix.kbcadam.intra [10.85.207.60]
# Query_time: 13 Lock_time: 0 Rows_sent: 1 Rows_examined: 358966
select value from history_log where itemid=28926 order by id desc limit 1;

[[email protected]]

We noticed that MYSQL uses the index history_log_1 (itemid,clock) for this query. The second index history_log_2 (itemid,id) would be more appropriate.
I don't know how to force MYSQL to use this second index.
Another option would be to change Zabbix server code with an additional "USE INDEX (history_log_2)" at every query on history_log.

Any ideas ?

[untergeek]

We are using mysql 5.0.51 and zabbix 8.0.

I want access to your time machine! Running Zabbix 8.0 seems like it would be really awesome!