Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-12980

FORCE INDEX hints are not always respected

    Details

      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:

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                psergey Sergei Petrunia
                Reporter:
                jmuchemb Julien Muchembled
              • Votes:
                1 Vote for this issue
                Watchers:
                7 Start watching this issue

                Dates

                • Created:
                  Updated: