JoaoJandre commented on PR #8737: URL: https://github.com/apache/cloudstack/pull/8737#issuecomment-2003875216
Sure @vishesh92 , here are the results: No index: ``` MariaDB [teste]> ANALYZE SELECT vm_stats.id, vm_stats.vm_id, vm_stats.mgmt_server_id, vm_stats.timestamp, vm_stats.vm_stats_data FROM vm_stats WHERE vm_stats.vm_id = 368 ORDER BY vm_stats.timestamp DESC; +------+-------------+----------+------+---------------+------+---------+------+----------+----------+----------+------------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+----------+------+---------------+------+---------+------+----------+----------+----------+------------+-----------------------------+ | 1 | SIMPLE | vm_stats | ALL | NULL | NULL | NULL | NULL | 39285187 | 40866.00 | 100.00 | 100.00 | Using where; Using filesort | +------+-------------+----------+------+---------------+------+---------+------+----------+----------+----------+------------+-----------------------------+ 1 row in set (27.276 sec) ``` Index on (`vm_id`, `timestamp`): ``` MariaDB [teste]> ANALYZE SELECT vm_stats.id, vm_stats.vm_id, vm_stats.mgmt_server_id, vm_stats.timestamp, vm_stats.vm_stats_data FROM vm_stats WHERE vm_stats.vm_id = 368 ORDER BY vm_stats.timestamp DESC; +------+-------------+----------+------+---------------+--------+---------+-------+-------+----------+----------+------------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+----------+------+---------------+--------+---------+-------+-------+----------+----------+------------+-------------+ | 1 | SIMPLE | vm_stats | ref | index2 | index2 | 8 | const | 87578 | 40866.00 | 100.00 | 100.00 | Using where | +------+-------------+----------+------+---------------+--------+---------+-------+-------+----------+----------+------------+-------------+ 1 row in set (4.544 sec) ``` Index on (`vm_id`): ``` MariaDB [teste]> ANALYZE SELECT vm_stats.id, vm_stats.vm_id, vm_stats.mgmt_server_id, vm_stats.timestamp, vm_stats.vm_stats_data FROM vm_stats WHERE vm_stats.vm_id = 368 ORDER BY vm_stats.timestamp DESC; +------+-------------+----------+------+---------------+--------+---------+-------+-------+----------+----------+------------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+----------+------+---------------+--------+---------+-------+-------+----------+----------+------------+-----------------------------+ | 1 | SIMPLE | vm_stats | ref | index2 | index2 | 8 | const | 87528 | 40866.00 | 100.00 | 100.00 | Using where; Using filesort | +------+-------------+----------+------+---------------+--------+---------+-------+-------+----------+----------+------------+-----------------------------+ 1 row in set (8.123 sec) ``` The results/conclusions are the same: No index is very slow; the (`vm_id`, `timestamp`) index is the fastest for listing when considering the `timestamp`, and the (`vm_id`) index is much faster than without index, but a bit slower than the (`vm_id`, `timestamp`) index. One point to consider is that this query is returning one whole week's worth of data for a VM and ordering it. That amounts to 40866 rows in this example (that have to be selected from 40 million rows in the table); It's expected that it should take some time. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@cloudstack.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
