- Issue created by @daluxz
- Merge request !803542400 - added update hook which adds index for the poll_vote table and... → (Open) created by Unnamed author
We are seeing a lot of slow queries on the poll_vote table. We have about 500 polls on our website with over 1 million votes.
Every time the SELECT chid, COUNT(chid) AS votes FROM {poll_vote} WHERE pid = :pid GROUP BY chid query is run, a full table scan is performed, hitting all the votes.
Example from our slow query logs:
# Query_time: 13.506124 Lock_time: 0.000003 Rows_sent: 3 Rows_examined: 1114909
SET timestamp=1741591045;
SELECT chid, COUNT(chid) AS votes FROM "poll_vote" WHERE pid = '167' GROUP BY chid;
Locally this query takes "only" 2 seconds, but it is still performing a full table scan.
MariaDB [db]> SELECT chid, COUNT(chid) AS votes FROM poll_vote WHERE pid = '167' GROUP BY chid;
+------+-------+
| chid | votes |
+------+-------+
| 457 | 1065 |
| 458 | 269 |
| 459 | 692 |
+------+-------+
3 rows in set (2.235 sec)The explain query shows that no index is used:
MariaDB [db]> EXPLAIN SELECT chid, COUNT(chid) AS votes FROM poll_vote WHERE pid = '167' GROUP BY chid;
+------+-------------+-----------+-------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------+-------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | poll_vote | index | NULL | chid | 4 | NULL | 1219500 | Using where |
+------+-------------+-----------+-------+---------------+------+---------+------+---------+-------------+
1 row in set (0.000 sec)
Adding an extra index (using both chid and pid) seems to improve this a lot.
MariaDB [db]> SHOW INDEX FROM poll_vote;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| poll_vote | 0 | PRIMARY | 1 | id | A | 1205272 | NULL | NULL | | BTREE | | |
| poll_vote | 1 | chid | 1 | chid | A | 1880 | NULL | NULL | | BTREE | | |
| poll_vote | 1 | hostname | 1 | hostname | A | 25109 | NULL | NULL | | BTREE | | |
| poll_vote | 1 | uid | 1 | uid | A | 178 | NULL | NULL | | BTREE | | |
| poll_vote | 1 | chid_pid | 1 | chid | A | 1323 | NULL | NULL | | BTREE | | |
| poll_vote | 1 | chid_pid | 2 | pid | A | 1764 | NULL | NULL | | BTREE | | |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.000 sec)
Now the query runs a lot faster:
MariaDB [db]> SELECT chid, COUNT(chid) AS votes FROM poll_vote WHERE pid = '167' GROUP BY chid;
+------+-------+
| chid | votes |
+------+-------+
| 457 | 1065 |
| 458 | 269 |
| 459 | 692 |
+------+-------+
3 rows in set (0.242 sec)The explain query shows the new index is being used.
MariaDB [db]> EXPLAIN SELECT chid, COUNT(chid) AS votes FROM poll_vote WHERE pid = '167' GROUP BY chid;
+------+-------------+-----------+-------+---------------+--------+---------+------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------+-------+---------------+--------+---------+------+---------+--------------------------+
| 1 | SIMPLE | poll_vote | index | NULL | chid_2 | 8 | NULL | 1205272 | Using where; Using index |
+------+-------------+-----------+-------+---------------+--------+---------+------+---------+--------------------------+
1 row in set (0.000 sec)So before adding the index, it took some 2 seconds to perform the query, and after adding the index the query only took 0.242 seconds, which is quite a big improvement.
The downside of adding an extra index will be slower write/delete/update queries, and a bigger database, but I think the benefits outweigh that.
I will add a patch.
Active
2.0
Code