Re: index cannot be used when passing 'int' to 'varchar'
> they both using index?!
Yes. The second one is "Using index" because all the fields in the SELECT are in the index! Add a second column to the table, and "Using index" will vanish (because of the "*"). You can see part of this by doing
EXPLAIN EXTENDED SELECT SQL_NO_CACHE * from employees.ttt where emp_no=19967;
SHOW WARNINGS; -- immediately after the EXPLAIN EXTENDED.
The WARNING will say something like
SELECT `employees`.`ttt`.`emp_no` FROM `employees`.`ttt` WHERE `employees`.`ttt`.`emp_no` = 19967.
A table with a single column and many rows is virtually useless. Some of the anomalies you are encountering are from the artificial nature of the benchmark.
Perhaps the only thing you have 'proven' is that
where emp_no=19967;
is not evaluated the way you expect. I suspect that is evaluated as
WHERE CONVERT(emp_no, UNSIGNED) = 19967
MySQL cannot efficiently use an index when the column is hidden in a function call. Hence, the need to scan the entire index.
<opinion>
Benchmarks often lead to confusion and misinformation.
</opinion>
Subject
Views
Written By
Posted
3036
June 10, 2012 11:16PM
1360
June 11, 2012 11:27PM
1312
June 12, 2012 12:48AM
Re: index cannot be used when passing 'int' to 'varchar'
1374
June 12, 2012 09:23PM
1559
June 12, 2012 11:16PM
1308
June 13, 2012 07:15PM
1306
June 13, 2012 10:35PM
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.