Why primary key has no good effect on select?

Question

It's my table t1; It has one million rows.

CREATE TABLE `t1` (
  `a` varchar(10) NOT NULL,
  `b` varchar(10) DEFAULT NULL,
  `c` varchar(10) DEFAULT NULL,
  `d` varchar(10) DEFAULT NULL,
  `e` varchar(10) DEFAULT NULL,
  `f` varchar(10) DEFAULT NULL,
  `g` varchar(10) DEFAULT NULL,
  `h` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

Result:

mysql> select * from t1 where a=10000000;
Empty set (1.42 sec)

mysql> select * from t1 where b=10000000;
Empty set (1.41 sec)

Why select primary key is as fast as a normal field?

Primary key is also an index,and I have added another index to a,but it does not have any effect neither. — LTzycLT, Jan 15 '14 at 09:58
@Alexander, a primary key in MySQL is an index : http://stackoverflow.com/a/707896/1788704 — kmas, Jan 15 '14 at 09:59
Try `select * from t1 where a='10000000';`. You might be forcing MySQL to convert all of those strings to integers - in which case an index on the strings is useless. — Damien_The_Unbeliever, Jan 15 '14 at 10:02
@Damien_The_Unbeliever Turn your comment into an answer. I'm pretty certain that this is the cause of the issue. — Dennis Traub, Jan 15 '14 at 10:03
Don't compare apples (strings) and oranges (numbers). `'10000000'` is string, `10000000` is a number. — a_horse_with_no_name, Jan 15 '14 at 10:04
@Damien_The_Unbeliever Yes,you are right.Use your method,it becomes (0.00 sec) — LTzycLT, Jan 15 '14 at 10:06
@DennisTraub - it just goes to show my prejudice against MySQL really. I know that any *sensible* SQL database system will perform such a conversion, but I've come to not expect MySQL to do sensible things :-| — Damien_The_Unbeliever, Jan 15 '14 at 10:07
Hmm one would think mysql would be smart to convert the constant to integer instead of converting all the values in the column. — Petko Petkov, Jan 15 '14 at 10:08

Damien_The_Unbeliever · Accepted Answer · 2014-01-15T10:10:11.257

5

Try select * from t1 where a='10000000';.

You're probably forcing MySQL to convert all of those strings to integers - because integers have a higher type precedence than varchar - in which case an index on the strings is useless

Actually, apparently, I was slightly wrong. By my reading of the conversions documentation, I believe that in MySQL we end up forcing both sides of the comparison to be converted to float, since I can't see any bullet point above:

In all other cases, the arguments are compared as floating-point (real) numbers.

that would match a string on one side and an integer on the other.

edited Jan 15 '14 at 10:10

answered Jan 15 '14 at 10:04

Damien_The_Unbeliever

227,877
22
326
423

2

At last I found some useful information from mysql.com "For comparisons of a string column with a number, MySQL cannot use an index on the column to look up the value quickly." "The reason for this is that there are many different strings that may convert to the value 1, such as '1', ' 1', or '1a'." – LTzycLT Jan 15 '14 at 10:30

score 1 · Answer 2 · answered Jan 15 '14 at 10:04

Data is stored in blocks in almost all databases. Reading a block is an elementary Unit of IO. Indexes helps the system in zeroing in on the datablock which holds the data that we are trying to read and would avoid reading all the datablocks. In a very small table which has single or very few data blocks the usage of index could actually be a overhead and might be skipped altogether. Even if used, the indexes would rarely provide any performance benefit. Try the same experiment on a rather large table.

PS: Indexes and Key (Primary Keys) are not interchangeable concepts. The Former is Physical and the latter is logical.

Why primary key has no good effect on select?

2 Answers2