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

Index is not used on a partition when using like in a statement

    XMLWordPrintable

    Details

      Description

      I created a table like this:

      CREATE TABLE `cdr_03` (
        `TimeStamp` datetime DEFAULT NULL,
        `Ses` varchar(10) DEFAULT NULL,
        `Session` varchar(50) DEFAULT NULL,
        `IP` varchar(20) DEFAULT NULL,
        `IMSI` varchar(30) DEFAULT NULL,
        `APN` varchar(300) DEFAULT NULL,
        `MCC` int(11) DEFAULT NULL,
        `MNC` int(11) DEFAULT NULL,
        `ECGI` int(11) DEFAULT NULL,
        `TAI` int(11) DEFAULT NULL,
        `NAS` varchar(30) DEFAULT NULL,
        `IMEI` varchar(30) DEFAULT NULL,
        `UPD` datetime DEFAULT NULL,
        KEY `IP` (`IP`),
        KEY `IMSI` (`IMSI`),
        KEY `APN` (`APN`),
        KEY `IMEI` (`IMEI`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1
       PARTITION BY HASH (to_days(`TimeStamp`))
      PARTITIONS 2300
      

      then I loaded about 600 partitions with 2M records each.

      using this query:

      select * from cdr_03 where TimeStamp >= "2018-01-01 00:00:00" and TimeStamp <= "2018-01-01 23:59:59" and IP = "343.456.989.659";
      

      I get a reply with one record in about 8 seconds.

      using this query:

      select * from cdr_03 where TimeStamp >= "2018-01-01 00:00:00" and TimeStamp <= "2018-01-01 23:59:59" and IP like "343.456.989.65%";
      

      after 48 hours the query still running. There is only 1 record in that interval and the wild card is covering only 1 char.

      the explain of the 2 queries is:

      explain select * from cdr_03 where TimeStamp >= "2018-01-01 00:00:00" and TimeStamp <= "2018-01-01 23:59:59" and IP = "343.456.989.659";
       
      +------+-------------+--------+------+---------------+------+---------+-------+------+-------------+
      | id   | select_type | table  | type | possible_keys | key  | key_len | ref   | rows | Extra       |
      +------+-------------+--------+------+---------------+------+---------+-------+------+-------------+
      |    1 | SIMPLE      | cdr_03 | ref  | IP            | IP   | 23      | const | 2300 | Using where |
      +------+-------------+--------+------+---------------+------+---------+-------+------+-------------+
      

      explain select * from cdr_03 where TimeStamp >= "2018-01-01 00:00:00" and TimeStamp <= "2018-01-01 23:59:59" and IP like "343.456.989.65%";
       
      +------+-------------+--------+-------+---------------+------+---------+------+------+-------------+
      | id   | select_type | table  | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
      +------+-------------+--------+-------+---------------+------+---------+------+------+-------------+
      |    1 | SIMPLE      | cdr_03 | range | IP            | IP   | 23      | NULL | 2300 | Using where |
      +------+-------------+--------+-------+---------------+------+---------+------+------+-------------+
      

      The ref field, in the query using the like, is set to NULL, but according to the documentation, the like is supposed to use the index if the % is not at the beginning.
      Also, there is only 1 record in that interval and the partition contains only 2M rows.

        Attachments

          Activity

            People

            Assignee:
            serg Sergei Golubchik
            Reporter:
            luigius Luigi Caramico
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.