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

[liao zhifeng]

thank you for your suggesion

mysql> show create table employees.ttt\G
*************************** 1. row ***************************
Table: ttt
Create Table: CREATE TABLE `ttt` (
`emp_no` varchar(14) default NULL,
KEY `idx_varchar` (`emp_no`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show index from employees.ttt\G
*************************** 1. row ***************************
Table: ttt
Non_unique: 1
Key_name: idx_varchar
Seq_in_index: 1
Column_name: emp_no
Collation: A
Cardinality: 300024
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
1 row in set (0.00 sec)

vimix:~ # mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.41-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> \c
mysql> explain select SQL_NO_CACHE * from employees.ttt where emp_no='19967';
+----+-------------+-------+------+---------------+-------------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+--------------------------+
| 1 | SIMPLE | ttt | ref | idx_varchar | idx_varchar | 17 | const | 1 | Using where; Using index |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

mysql> exit
Bye
vimix:~ # service mysqld restart
Shutting down MySQL... done
Starting MySQL done
vimix:~ # mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.41-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> \c
mysql> explain select SQL_NO_CACHE * from employees.ttt where emp_no=19967;
+----+-------------+-------+-------+---------------+-------------+---------+------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------------+---------+------+--------+--------------------------+
| 1 | SIMPLE | ttt | index | idx_varchar | idx_varchar | 17 | NULL | 300024 | Using where; Using index |
+----+-------------+-------+-------+---------------+-------------+---------+------+--------+--------------------------+
1 row in set (0.01 sec)

they both using index?!