MySQL Forums
Forum List  »  Optimizer & Parser

Re: index cannot be used when passing 'int' to 'varchar'
Posted by: liao zhifeng
Date: June 12, 2012 12:48AM

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?!

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: index cannot be used when passing 'int' to 'varchar'
1265
June 12, 2012 12:48AM


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.