Details
-
Task
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
Description
With the following secondary index (link to full schema):
KEY (`partition`, oid, tid),
|
I have for example:
MariaDB [neo0]> analyze SELECT SQL_NO_CACHE MAX(oid) AS oid FROM obj GROUP BY `partition`;
|
+------+-------------+-------+-------+---------------+---------+---------+------+----------+-------------+----------+------------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+------+----------+-------------+----------+------------+-------------+
|
| 1 | SIMPLE | obj | index | NULL | PRIMARY | 18 | NULL | 69066464 | 69066464.00 | 100.00 | 100.00 | Using index |
|
+------+-------------+-------+-------+---------------+---------+---------+------+----------+-------------+----------+------------+-------------+
|
1 row in set (22.01 sec)
|
 |
MariaDB [neo0]> analyze SELECT SQL_NO_CACHE MAX(oid) AS oid FROM obj FORCE INDEX FOR GROUP BY (`partition`) GROUP BY `partition`;
|
+------+-------------+-------+-------+---------------+-----------+---------+------+----------+-------------+----------+------------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|
+------+-------------+-------+-------+---------------+-----------+---------+------+----------+-------------+----------+------------+-------------+
|
| 1 | SIMPLE | obj | index | NULL | partition | 18 | NULL | 69066464 | 69066464.00 | 100.00 | 100.00 | Using index |
|
+------+-------------+-------+-------+---------------+-----------+---------+------+----------+-------------+----------+------------+-------------+
|
1 row in set (27.40 sec)
|
After ANALYZE TABLE obj:
MariaDB [neo0]> analyze SELECT SQL_NO_CACHE MAX(oid) AS oid FROM obj GROUP BY `partition`;
|
+------+-------------+-------+-------+---------------+-----------+---------+------+------+--------+----------+------------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|
+------+-------------+-------+-------+---------------+-----------+---------+------+------+--------+----------+------------+--------------------------+
|
| 1 | SIMPLE | obj | range | NULL | partition | 2 | NULL | 285 | 128.00 | 100.00 | 100.00 | Using index for group-by |
|
+------+-------------+-------+-------+---------------+-----------+---------+------+------+--------+----------+------------+--------------------------+
|
1 row in set (0.19 sec)
|
(this last result is what we expect)
Here, I am not talking about an engine having so bad statistics that an inefficient query plan is chosen. Of course:
- some engines may be better than other at providing good statistics, and there may be bugs to fix about this
- and our code would be easier to write if we didn't have to give index hints
But good statistics in our project is not really important. We have a fixed list of SQL statements and for each of them, the query plan must always be the same. Indexes are carefully chosen for this.
Because engines can not guarantee that statistics are always good enough, we'll end up adding FORCE hints everywhere and we expect MariaDB to follow them blindly.
We had this issue with both InnoDB and TokuDB.
For us, this is the most critical bug we have because it can kill our application in unresolvable ways. In the past, we already had optimizer issues on performance-critical queries and fortunately index hints did the job at the time:
- mysql: force _getNextTID() to use appropriate/whole index
- mysql: fix use of wrong SQL index when checking for dropped partitions (here, OPTIMIZE TABLE obj didn't help)
Attachments
Issue Links
- relates to
-
-
- Closed
-