index cannot be used when passing 'int' to 'varchar'

[liao zhifeng]

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)