MySQL Forums
Forum List  »  Optimizer & Parser

index cannot be used when passing 'int' to 'varchar'
Posted by: liao zhifeng
Date: June 10, 2012 11:16PM

I have one table with varchar type and created the index.
query like this:
select SQL_NO_CACHE * from employees.ttt where emp_no=10021
I find the index not used. it's take so many time to finish the query

but if I change the type to int , created the index,
query like this:
select SQL_NO_CACHE * from employees.ttt where emp_no='10021'
the index will be used


varchar can use the int index but int cannot use the varchar index
I want to know why?


some test:
vimix:~ # service mysqld restart
Shutting down MySQL. done
Starting MySQL done
vimix:~ # /usr/local/mysql/mysqlslap --concurrency=25 --iterations=1 --create-schema=employees --query=/tmp/testsqlint -S/tmp/mysql.sock
Benchmark
Average number of seconds to run all queries: 9.440 seconds
Minimum number of seconds to run all queries: 9.440 seconds
Maximum number of seconds to run all queries: 9.440 seconds
Number of clients running queries: 25
Average number of queries per client: 9998

vimix:~ # service mysqld restart
Shutting down MySQL... done
Starting MySQL done
vimix:~ # /usr/local/mysql/mysqlslap --concurrency=25 --iterations=1 --create-schema=employees --query=/tmp/testsqlvar -S/tmp/mysql.sock
Benchmark
Average number of seconds to run all queries: 12.465 seconds
Minimum number of seconds to run all queries: 12.465 seconds
Maximum number of seconds to run all queries: 12.465 seconds
Number of clients running queries: 25
Average number of queries per client: 9998

vimix:~ # service mysqld restart
Shutting down MySQL.. done
Starting MySQL done
vimix:~ # /usr/local/mysql/mysqlslap --concurrency=25 --iterations=1 --create-schema=employees --query=/tmp/testsqlvar -S/tmp/mysql.sock
Benchmark
Average number of seconds to run all queries: 16.474 seconds
Minimum number of seconds to run all queries: 16.474 seconds
Maximum number of seconds to run all queries: 16.474 seconds
Number of clients running queries: 25
Average number of queries per client: 9998
vimix:~ # service mysqld restart
Shutting down MySQL.. done
Starting MySQL done
vimix:~ # /usr/local/mysql/mysqlslap --concurrency=25 --iterations=1 --create-schema=employees --query=/tmp/testsqlvar -S/tmp/mysql.sock
.................very long time..............still can't get the result

two tables
mysql> desc tt;
+--------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| emp_no | int(11) | YES | MUL | NULL | |
+--------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> desc ttt;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| emp_no | varchar(14) | YES | MUL | NULL | |
+--------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> show index from tt;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| tt | 1 | idx_int | 1 | emp_no | A | 300024 | NULL | NULL | YES | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.02 sec)

mysql> show index from ttt;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| ttt | 1 | idx_varchar | 1 | emp_no | A | 300024 | NULL | NULL | YES | BTREE | |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.10 sec)

Options: ReplyQuote


Subject
Views
Written By
Posted
index cannot be used when passing 'int' to 'varchar'
2906
June 10, 2012 11:16PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.