mysql: force _getNextTID() to use appropriate/whole index
mysql: Fix use of wrong SQL index in _getNextTID()
Similarly to 13911ca3 on the same instance after MariaDB was upgraded to
10.1.17 the following query, even after OPTIMIZE TABLE obj, started to execute
very slowly:
MariaDB [(none)]> SELECT tid FROM neo1.obj WHERE `partition`=5 AND oid=79613 AND tid>268707071353462798 ORDER BY tid LIMIT 1;
+--------------------+
| tid |
+--------------------+
| 268707072758797063 |
+--------------------+
1 row in set (4.82 sec)Both explain and analyze says the query will/is using partition key but only partially (note key_len is only 10, not 18):
MariaDB [(none)]> SHOW INDEX FROM neo1.obj;
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| obj | 0 | PRIMARY | 1 | partition | A | 28755928 | NULL | NULL | | BTREE | | |
| obj | 0 | PRIMARY | 2 | tid | A | 28755928 | NULL | NULL | | BTREE | | |
| obj | 0 | PRIMARY | 3 | oid | A | 28755928 | NULL | NULL | | BTREE | | |
| obj | 0 | partition | 1 | partition | A | 28755928 | NULL | NULL | | BTREE | | |
| obj | 0 | partition | 2 | oid | A | 28755928 | NULL | NULL | | BTREE | | |
| obj | 0 | partition | 3 | tid | A | 28755928 | NULL | NULL | | BTREE | | |
| obj | 1 | data_id | 1 | data_id | A | 28755928 | NULL | NULL | YES | BTREE | | |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.00 sec)
MariaDB [(none)]> explain SELECT tid FROM neo1.obj WHERE `partition`=5 AND oid=79613 AND tid>268707071353462798 ORDER BY tid LIMIT 1;
+------+-------------+-------+------+-------------------+-----------+---------+-------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+-------------------+-----------+---------+-------------+------+--------------------------+
| 1 | SIMPLE | obj | ref | PRIMARY,partition | partition | 10 | const,const | 2 | Using where; Using index |
+------+-------------+-------+------+-------------------+-----------+---------+-------------+------+--------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> analyze SELECT tid FROM neo1.obj WHERE `partition`=5 AND oid=79613 AND tid>268707071353462798 ORDER BY tid LIMIT 1;
+------+-------------+-------+------+-------------------+-----------+---------+-------------+------+------------+----------+------------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+------+-------------------+-----------+---------+-------------+------+------------+----------+------------+--------------------------+
| 1 | SIMPLE | obj | ref | PRIMARY,partition | partition | 10 | const,const | 2 | 9741121.00 | 100.00 | 0.00 | Using where; Using index |
+------+-------------+-------+------+-------------------+-----------+---------+-------------+------+------------+----------+------------+--------------------------+
1 row in set (4.93 sec)By explicitly forcing (partition, oid, tid) index usage which is precisely designed to serve this and similar queries can avoid the query from being slow:
MariaDB [(none)]> analyze SELECT tid FROM neo1.obj FORCE INDEX(`partition`) WHERE `partition`=5 AND oid=79613 AND tid>268707071353462798 ORDER BY tid LIMIT 1;
+------+-------------+-------+-------+---------------+-----------+---------+------+------+--------+----------+------------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+---------------+-----------+---------+------+------+--------+----------+------------+--------------------------+
| 1 | SIMPLE | obj | range | partition | partition | 18 | NULL | 2 | 1.00 | 100.00 | 100.00 | Using where; Using index |
+------+-------------+-------+-------+---------------+-----------+---------+------+------+--------+----------+------------+--------------------------+
1 row in set (0.00 sec)