Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6.11
-
None
-
None
-
None
-
Oracle Linux Server release 9.4
Description
SLEEP(1) AS bug causing "Using index; Using temporary; Using filesort" instead of proper optimal index being used even with forced USE INDEX.
SLEEP(1) AS bug removal fixes this issue.
Do to reasons unknown at this time, we see this issue only on our master and not slaves.
[Normal case]
The following queries on multiple slave servers shows expected index usage:
MariaDB [REDACTED]> explain extended SELECT document_srl AS id, SLEEP(1) AS bug FROM xe_documents ORDER BY document_srl DESC LIMIT 1;
|
+------+-------------+--------------+-------+---------------+---------+---------+------+------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+--------------+-------+---------------+---------+---------+------+------+----------+-------------+
|
| 1 | SIMPLE | xe_documents | index | NULL | PRIMARY | 8 | NULL | 1 | 100.00 | Using index |
|
+------+-------------+--------------+-------+---------------+---------+---------+------+------+----------+-------------+
|
1 row in set, 1 warning (0.001 sec)
|
|
|
MariaDB [REDACTED]> explain extended SELECT document_srl AS id, SLEEP(1) AS bug FROM xe_documents USE INDEX (PRIMARY) ORDER BY document_srl DESC LIMIT 1;
|
+------+-------------+--------------+-------+---------------+---------+---------+------+------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+--------------+-------+---------------+---------+---------+------+------+----------+-------------+
|
| 1 | SIMPLE | xe_documents | index | NULL | PRIMARY | 8 | NULL | 1 | 100.00 | Using index |
|
+------+-------------+--------------+-------+---------------+---------+---------+------+------+----------+-------------+
|
1 row in set, 1 warning (0.000 sec)
|
|
|
MariaDB [REDACTED]> explain extended SELECT document_srl AS id FROM xe_documents ORDER BY document_srl DESC LIMIT 1;
|
+------+-------------+--------------+-------+---------------+---------+---------+------+------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+--------------+-------+---------------+---------+---------+------+------+----------+-------------+
|
| 1 | SIMPLE | xe_documents | index | NULL | PRIMARY | 8 | NULL | 1 | 100.00 | Using index |
|
+------+-------------+--------------+-------+---------------+---------+---------+------+------+----------+-------------+
|
1 row in set, 1 warning (0.000 sec)
|
|
|
MariaDB [REDACTED]> explain extended SELECT document_srl AS id FROM xe_documents USE INDEX (PRIMARY) ORDER BY document_srl DESC LIMIT 1;
|
+------+-------------+--------------+-------+---------------+---------+---------+------+------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+--------------+-------+---------------+---------+---------+------+------+----------+-------------+
|
| 1 | SIMPLE | xe_documents | index | NULL | PRIMARY | 8 | NULL | 1 | 100.00 | Using index |
|
+------+-------------+--------------+-------+---------------+---------+---------+------+------+----------+-------------+
|
1 row in set, 1 warning (0.000 sec)
|
[Abnormal cases]
When query has SLEEP(1) AS bug on master, the extended explain shows
"Using index; Using temporary; Using filesort", see below:
MariaDB [REDACTED]> explain extended SELECT document_srl AS id, SLEEP(1) AS bug FROM xe_documents ORDER BY document_srl DESC LIMIT 1;
|
+------+-------------+--------------+-------+---------------+---------------+---------+------+-----------+----------+----------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+--------------+-------+---------------+---------------+---------+------+-----------+----------+----------------------------------------------+
|
| 1 | SIMPLE | xe_documents | index | NULL | idx_is_secret | 4 | NULL | 126906236 | 100.00 | Using index; Using temporary; Using filesort |
|
+------+-------------+--------------+-------+---------------+---------------+---------+------+-----------+----------+----------------------------------------------+
|
1 row in set, 1 warning (0.000 sec)
|
|
|
MariaDB [REDACTED]> explain extended SELECT document_srl AS id, SLEEP(1) AS bug FROM xe_documents USE INDEX (PRIMARY) ORDER BY document_srl DESC LIMIT 1;
|
+------+-------------+--------------+-------+---------------+---------+---------+------+-----------+----------+----------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+--------------+-------+---------------+---------+---------+------+-----------+----------+----------------------------------------------+
|
| 1 | SIMPLE | xe_documents | index | NULL | PRIMARY | 8 | NULL | 126906236 | 100.00 | Using index; Using temporary; Using filesort |
|
+------+-------------+--------------+-------+---------------+---------+---------+------+-----------+----------+----------------------------------------------+
|
1 row in set, 1 warning (0.000 sec)
|
|
|
MariaDB [REDACTED]> explain extended SELECT document_srl AS id FROM xe_documents ORDER BY document_srl DESC LIMIT 1;
|
+------+-------------+--------------+-------+---------------+---------+---------+------+------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+--------------+-------+---------------+---------+---------+------+------+----------+-------------+
|
| 1 | SIMPLE | xe_documents | index | NULL | PRIMARY | 8 | NULL | 1 | 100.00 | Using index |
|
+------+-------------+--------------+-------+---------------+---------+---------+------+------+----------+-------------+
|
1 row in set, 1 warning (0.000 sec)
|
|
|
MariaDB [REDACTED]> explain extended SELECT document_srl AS id FROM xe_documents USE INDEX (PRIMARY) ORDER BY document_srl DESC LIMIT 1;
|
+------+-------------+--------------+-------+---------------+---------+---------+------+------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+--------------+-------+---------------+---------+---------+------+------+----------+-------------+
|
| 1 | SIMPLE | xe_documents | index | NULL | PRIMARY | 8 | NULL | 1 | 100.00 | Using index |
|
+------+-------------+--------------+-------+---------------+---------+---------+------+------+----------+-------------+
|
1 row in set, 1 warning (0.000 sec)
|