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

Excessive "Using where" when using varchar/char type index

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 5.5.27, 5.3.8, 5.2.12, 5.1.62
    • Fix Version/s: 5.5
    • Component/s: None
    • Labels:
      None
    • Environment:
      all

      Description

      When using varchar/char type of index , there is excessive "Using where" in the result of explain command.
      This cause unnecessary string comparison in query.

      test case:

      CREATE TABLE `tmp_xf_like` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `user_id` bigint(20) NOT NULL,
        `title` varchar(128) NOT NULL,
        `memo` varchar(2000) DEFAULT NULL,
        PRIMARY KEY (`id`),
        KEY `idx_userid` (`title`),
        KEY `user_id` (`user_id`)
      ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
       
      mysql> explain select id from tmp_xf_like where title='a';
      +----+-------------+-------------+------+---------------+------------+---------+-------+------+--------------------------+
      | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
      +----+-------------+-------------+------+---------------+------------+---------+-------+------+--------------------------+
      | 1 | SIMPLE | tmp_xf_like | ref | idx_userid | idx_userid | 386 | const | 1 | Using where; Using index |
      +----+-------------+-------------+------+---------------+------------+---------+-------+------+--------------------------+
      1 row in set (0.01 sec)

      The result is the same after changing field "title" into binary
      But INT type looks normal.

      explain select id from tmp_xf_like where user_id=1;
      +----+-------------+-------------+------+---------------+---------+---------+-------+------+-------------+
      | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
      +----+-------------+-------------+------+---------------+---------+---------+-------+------+-------------+
      | 1 | SIMPLE | tmp_xf_like | ref | user_id | user_id | 8 | const | 1 | Using index |
      +----+-------------+-------------+------+---------------+---------+---------+-------+------+-------------+
      1 row in set (0.00 sec)

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              dingqi.lxb Xiaobin Lin (Inactive)
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated: