Bug #67006 unnecessary use of ICP for a single predicate

• Submitted: 28 Sep 2012 10:30
• Modified: 10 Jan 2013 13:58
• Reporter: Arnaud Adant
• Status: Duplicate
• Category: MySQL Server: Optimizer
• Severity: S5 (Performance)
• Version: 5.6
• OS: Any
• CPU Architecture: Any

[28 Sep 2012 10:30] Arnaud Adant

Description:
This bug comes from this one : Bug #66983      unnecessary "Using where " in explain result

In the test case, ICP is used when it is not necessary.

mysql> explain select * 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 index condition |
+----+-------------+-------------+------+---------------+------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

Here there is no reason to use ICP because there is no benefit for a single predicate.

How to repeat:
CREATE TABLE `tmp_xf_like` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) DEFAULT 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 

explain select * from tmp_xf_like where title='a';

Suggested fix:
Do not use ICP in this case.

[8 Oct 2012 12:14] Olav Sandstå

The reason for ICP being used here is that:

1. The WHERE predicate is part of the table's condition also after
   optimization. The ICP code will try to push down any conditions that
   can be evaluated using the index.

2. The reason the WHERE predicate is part of the table's condition is
   the following code in test_if_ref() (in sql_optimizer.cc):

      if (right_item->const_item() && !(right_item->is_null()))
      {
	/*
	  We can remove binary fields and numerical fields except float,
	  as float comparison isn't 100 % secure
	  We have to keep normal strings to be able to check for end spaces

          sergefp: the above seems to be too restrictive. Counterexample:
            create table t100 (v varchar(10), key(v)) default charset=latin1;
            insert into t100 values ('a'),('a ');
            explain select * from t100 where v='a';
          The EXPLAIN shows 'using Where'. Running the query returns both
          rows, so it seems there are no problems with endspace in the most
          frequent case?
	*/
	if (field->binary() &&
	    field->real_type() != MYSQL_TYPE_STRING &&
	    field->real_type() != MYSQL_TYPE_VARCHAR &&
	    (field->type() != MYSQL_TYPE_FLOAT || field->decimals() == 0))
	{
	  return !right_item->save_in_field_no_warnings(field, true);
	}
      }

In this case we include the WHERE predicate in the table's condition
even if it is handled by the ref access is due to following:

 a) The field's/table's character is different from the default character set.
 b) It is a VARCHAR field.

As the comment in the above code suggests, this might be too restrictive.

[10 Jan 2013 13:58] Erlend Dahl

Fixed as a duplicate of bug#66983