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

BINARY and COLLATE xxx_bin comparisions are not used for optimization in some cases

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0.14
    • 10.1.4
    • Optimizer
    • None

    Description

      When optimizing queries that have conditions on an indexed string column, we require that collation of the condition matches collation of the column.

      In some cases (e.g. in range optimizer) we don't require strict collation match and additionally allow BINARY comparison and COLLATE xxx_bin comparision, even on a case insensitive column. These loose rules could be used in some more cases where they are not used now:

      Example 1:

      DROP TABLE IF EXISTS t1;
      		 
      CREATE TABLE t1 (c1 VARCHAR(20) CHARACTER SET latin1, PRIMARY KEY(c1));
      		 
      INSERT INTO t1 VALUES ('a'),('b'),('c'),('d');
      		 
      EXPLAIN SELECT * FROM t1 WHERE c1=BINARY 'a';

      returns:

      +------+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
      		 
      | id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
      		 
      +------+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
      		 
      |    1 | SIMPLE      | t1    | range | PRIMARY       | PRIMARY | 22      | NULL |    1 | Using where; Using index |
      		 
      +------+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+

      It could use "const" access instead.

      Example 2:

      DROP TABLE IF EXISTS t1,t2;
      		 
      CREATE TABLE t1 (c1 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_bin);
      		 
      INSERT INTO t1 VALUES ('a');
      		 
      CREATE TABLE t2 (c1 VARCHAR(10) CHARACTER SET latin1, PRIMARY KEY(c1));
      		 
      INSERT INTO t2 VALUES ('a'),('b');
      		 
      SELECT * FROM t1, t2 WHERE t1.c1=t2.c1;
      		 
      EXPLAIN SELECT * FROM t1, t2 WHERE t1.c1=t2.c1;

      returns

      +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+
      		 
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                          |
      		 
      +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+
      		 
      |    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    1 |                                                |
      		 
      |    1 | SIMPLE      | t2    | ALL  | PRIMARY       | NULL | NULL    | NULL |    2 | Range checked for each record (index map: 0x1) |
      		 
      +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+

      It could use "eq_ref" instead of "ALL + Range changed for each record".

      Example 3:

      DROP TABLE IF EXISTS t1,t2;
      		 
      CREATE TABLE t1 (c1 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_bin);
      		 
      INSERT INTO t1 VALUES ('a');
      		 
      CREATE TABLE t2 (c1 VARCHAR(10) CHARACTER SET latin1, PRIMARY KEY(c1));
      		 
      INSERT INTO t2 VALUES ('a'),('b');
      		 
      SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
      		 
      EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);

      returns

      +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+
      		 
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                          |
      		 
      +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+
      		 
      |    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    1 |                                                |
      		 
      |    1 | SIMPLE      | t2    | ALL  | PRIMARY       | NULL | NULL    | NULL |    2 | Range checked for each record (index map: 0x1) |
      		 
      +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+

      It could safely eliminate table t2 from the query.

      Attachments

        Activity

          People

            bar Alexander Barkov
            bar Alexander Barkov
            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.