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

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

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Minor
    • Resolution: Unresolved
    • 5.5.27, 5.3.8, 5.2.12, 5.1.62
    • 5.5(EOL)
    • None
    • None
    • 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

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

            Dates

              Created:
              Updated:

              Git Integration

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