| Bug #61377 | index not used when filtering with a user defined variable | ||
|---|---|---|---|
| Submitted: | 1 Jun 2011 14:20 | Modified: | 2 Jun 2011 8:44 |
| Reporter: | Cyril SCETBON | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.5.9 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | INDEX, udv | ||
[1 Jun 2011 14:30]
Valeriy Kravchuk
Looks like this is a problem of different collations/character sets. Please, send the output of: show create table t5\G select charset(@c), collation(@c);
[1 Jun 2011 14:40]
Cyril SCETBON
mysql> show create table t5\G
*************************** 1. row ***************************
Table: t5
Create Table: CREATE TABLE `t5` (
`id` int(11) DEFAULT NULL,
`c` varchar(30) DEFAULT NULL,
KEY `c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)
mysql> select charset(@c), collation(@c);
+-------------+-----------------+
| charset(@c) | collation(@c) |
+-------------+-----------------+
| utf8 | utf8_general_ci |
+-------------+-----------------+
1 row in set (0.00 sec)
mysql> alter table t5 engine innodb default charset='UTF8';
Query OK, 32 rows affected (0.05 sec)
Records: 32 Duplicates: 0 Warnings: 0
mysql> explain select * from t5 force index(c) where c=@c\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t5
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 32
Extra: Using where
1 row in set (0.00 sec)
mysql> explain select * from t5 where c=concat('cyril',5)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t5
type: ref
possible_keys: c
key: c
key_len: 33
ref: const
rows: 2
Extra: Using where
1 row in set (0.00 sec)
not better :(
[1 Jun 2011 15:37]
Valeriy Kravchuk
You have to set charset for varchar(30) column explicitly. Look:
macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.14-debug Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create table t5(id int,c varchar(30));
Query OK, 0 rows affected (0.18 sec)
mysql> insert into t5 values(1,'cyril1');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t5 select id+1,concat('cyril',id+1) from t5;
Query OK, 1 row affected (0.07 sec)
Records: 1 Duplicates: 0 Warnings: 0
...
mysql> insert into t5 select id+6,concat('cyril',id+6) from t5;
Query OK, 16 rows affected (0.00 sec)
Records: 16 Duplicates: 0 Warnings: 0
mysql> alter table t5 add index(c);
Query OK, 0 rows affected (0.26 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select @c:=concat('cyril',5);
+-----------------------+
| @c:=concat('cyril',5) |
+-----------------------+
| cyril5 |
+-----------------------+
1 row in set (0.02 sec)
mysql> show create table t5\G
*************************** 1. row ***************************
Table: t5
Create Table: CREATE TABLE `t5` (
`id` int(11) DEFAULT NULL,
`c` varchar(30) DEFAULT NULL,
KEY `c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.02 sec)
mysql> select collation(@c), charset(@c);
+-----------------+-------------+
| collation(@c) | charset(@c) |
+-----------------+-------------+
| utf8_general_ci | utf8 |
+-----------------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from t5 where c=concat('cyril',5)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t5
type: ref
possible_keys: c
key: c
key_len: 33
ref: const
rows: 2
Extra: Using where
1 row in set (0.05 sec)
mysql> explain select * from t5 where c=@c\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t5
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 32
Extra: Using where
1 row in set (0.00 sec)
mysql> alter table t5 engine innodb default charset='UTF8';
Query OK, 32 rows affected (0.15 sec)
Records: 32 Duplicates: 0 Warnings: 0
mysql> explain select * from t5 where c=@c\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t5
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 32
Extra: Using where
1 row in set (0.01 sec)
mysql> explain select * from t5 force index(c) where c=@c\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t5
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 32
Extra: Using where
1 row in set (0.00 sec)
mysql> explain extended select * from t5 where c=@c\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t5
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 32
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.01 sec)
mysql> show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: select `test`.`t5`.`id` AS `id`,`test`.`t5`.`c` AS `c` from `test`.`t5` where (convert(`test`.`t5`.`c` using utf8) = (@c))
1 row in set (0.00 sec)
mysql> alter table t5 modify c varchar(30) charset utf8;
Query OK, 32 rows affected (0.14 sec)
Records: 32 Duplicates: 0 Warnings: 0
mysql> explain select * from t5 force index(c) where c=@c\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t5
type: ref
possible_keys: c
key: c
key_len: 93
ref: const
rows: 2
Extra: Using where
1 row in set (0.00 sec)
mysql> explain select * from t5 where c=@c\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t5
type: ref
possible_keys: c
key: c
key_len: 93
ref: const
rows: 2
Extra: Using where
1 row in set (0.00 sec)
[1 Jun 2011 15:54]
Cyril SCETBON
ok I see. However, I provide a constant that should be translated one time. It shouldn't convert the column value each time (for each row) before comparing it to the value provided, but should do it on the value :
mysql> show create table t5\G
*************************** 1. row ***************************
Table: t5
Create Table: CREATE TABLE `t5` (
`id` int(11) DEFAULT NULL,
`c` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
KEY `c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
I see the latin1 used. But even if I provide an utf8 constant string, MySQL convert it one time. why shouldn't it do the same thing with a udv ?
mysql> explain select * from t5 where c= _utf8 'cyril5'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t5
type: ref
possible_keys: c
key: c
key_len: 33
ref: const
rows: 2
Extra: Using where
1 row in set (0.00 sec)
[1 Jun 2011 15:56]
Cyril SCETBON
Are you saying we can't use a udv when a column does not use utf8 ? I really think it's a bug
[1 Jun 2011 16:28]
Peter Laursen
I think there should be an option to define CHARSET for udv's in order to cast them to another charset than default. SET @pl = 'abc' CHARSET latin1; .. maybe something like this can already be achieved somehow? Peter (not a MySQL person)
[2 Jun 2011 3:52]
Valeriy Kravchuk
Peter, There is a way, and it was presented in the earlier comments here: mysql> set @a = _latin1 'abc'; Query OK, 0 rows affected (0.00 sec) mysql> set @b = _utf8 'abc'; Query OK, 0 rows affected (0.00 sec) mysql> select charset(@a), charset(@b); +-------------+-------------+ | charset(@a) | charset(@b) | +-------------+-------------+ | latin1 | utf8 | +-------------+-------------+ 1 row in set (0.01 sec) User defined variable by default has charset utf8 because of the settings: mysql> select charset(@c); +-------------+ | charset(@c) | +-------------+ | utf8 | +-------------+ 1 row in set (0.00 sec) mysql> show variables like 'char%'; +--------------------------+---------------------------------------+ | Variable_name | Value | +--------------------------+---------------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /Users/openxs/dbs/5.5/share/charsets/ | +--------------------------+---------------------------------------+ 8 rows in set (0.00 sec)
[2 Jun 2011 3:56]
Valeriy Kravchuk
Cyril, Our manual explains this at http://dev.mysql.com/doc/refman/5.5/en/charset-collation-expressions.html: "If one of the sides has a Unicode character set, and another side has a non-Unicode character set, the side with Unicode character set wins, and automatic character set conversion is applied to the non-Unicode side." So, in your case latin1 column has to be converted to utf8 and, thus, index (based on latin1 collation) can not be used.
[2 Jun 2011 8:44]
Cyril SCETBON
ok valeriy you win :) I understand that I can use select @c:=concat(_latin1 'cyril',5) to not have to rebuild my table :) However, I think that the udv should take my default charset (latin1) as my table does. I don't understand why udv use another one (utf8) and so force me to convert it
Description: Hi, When I use a udv to set the filter value of a sql request, MySQL can't use the index even if I try to force it How to repeat: mysql> create table t5(id int,c varchar(30)); Query OK, 0 rows affected (0.06 sec) mysql> insert into t5 values(1,'cyril1'); Query OK, 1 row affected (0.00 sec) mysql> insert into t5 select id+1,concat('cyril',id+1) from t5; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into t5 select id+3,concat('cyril',id+3) from t5; Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> insert into t5 select id+4,concat('cyril',id+4) from t5; Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> insert into t5 select id+5,concat('cyril',id+5) from t5; Query OK, 8 rows affected (0.00 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> insert into t5 select id+6,concat('cyril',id+6) from t5; Query OK, 16 rows affected (0.00 sec) Records: 16 Duplicates: 0 Warnings: 0 mysql> alter table t5 add index(c); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select @c:=concat('cyril',5); +-----------------------+ | @c:=concat('cyril',5) | +-----------------------+ | cyril5 | +-----------------------+ 1 row in set (0.00 sec) mysql> explain select * from t5 where c=concat('cyril',5)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t5 type: ref possible_keys: c key: c key_len: 33 ref: const rows: 2 Extra: Using where 1 row in set (0.00 sec) mysql> explain select * from t5 where c=@c\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t5 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 32 Extra: Using where 1 row in set (0.01 sec) mysql> explain select * from t5 force index(c) where c=@c\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t5 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 32 Extra: Using where 1 row in set (0.00 sec) Suggested fix: use the same plan when the value is provided by a udv. Anyway, we shouldn't have to force the index (even if it's not working here) as the value is the same when using the udv or not