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

Index not used when mixing string and number literals in IN-condition

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Confirmed (View Workflow)
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 5.5, 10.1, 10.0
    • Fix Version/s: 10.2
    • Component/s: Optimizer
    • Labels:
    • Environment:
      Ubuntu 14.04

      Description

      PK is used for a simple lookup by number literal:

      MariaDB [music]> EXPLAIN EXTENDED SELECT * FROM items WHERE id = 2300103499779;
      +------+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------+
      | id   | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
      +------+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------+
      |    1 | SIMPLE      | items | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 |       |
      +------+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------+

      PK is also used for a simple lookup by string literal:

      MariaDB [music]> EXPLAIN EXTENDED SELECT * FROM items WHERE id = '2300103499779';
      +------+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------+
      | id   | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
      +------+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------+
      |    1 | SIMPLE      | items | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 |       |
      +------+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------+

      PK is not used when using a mix of string and number literals:

      MariaDB [music]> EXPLAIN EXTENDED SELECT * FROM items WHERE id IN ('2300103499779', 2300103499779);
      +------+-------------+-------+------+---------------+------+---------+------+----------+----------+-------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
      +------+-------------+-------+------+---------------+------+---------+------+----------+----------+-------------+
      |    1 | SIMPLE      | items | ALL  | PRIMARY       | NULL | NULL    | NULL | 16060958 |   100.00 | Using where |
      +------+-------------+-------+------+---------------+------+---------+------+----------+----------+-------------+

        Attachments

          Activity

            People

            Assignee:
            psergey Sergei Petrunia
            Reporter:
            crishoj Christian Rishøj
            Votes:
            3 Vote for this issue
            Watchers:
            6 Start watching this issue

              Dates

              Created:
              Updated: