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. — , 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
The way MySQL's doing it does flow logically. In its eyes it's not that it's converting all values in the index, it's just performing a comparison it can't optimise with an index, because it's not a comparison that's 1:1/round trip exact. Because for example more than one value in the index may evaluate to that integer (the string 000001000000 vs the string 1000000, etc), so you'd have to iterate over all the values in the database to find all those. You haven't asked it to find the string '1000000', you've asked it to find all values than when converted, convert to 1000000 — thomasrutter, May 02 '23 at 00:46
I made an answer out of my above comment, it reads a bit better than the comment too. Bottom line is MySQL would be disobeying the user if it converted the 1000000 to string and it would have a different result for strings like '001000000abcdef' which still compare positively to the integer 1000000 — thomasrutter, May 02 '23 at 01:46

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

234,701
27
340
448

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 0 · 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.

thomasrutter · Answer 3 · 2023-05-02T02:01:22.557

There's a technical distinction in what you have asked MySQL for that doesn't seem important but is.

The values are all stored as strings, and you've asked MySQL to find strings that match the integer 1000000. This is a comparison it can't optimise by replacing the comparison with an index lookup, for reasons below.

Why can't MySQL just convert my integer 1000000 to a string and do that lookup with the index?

Because that would be asking for something that is subtly different. Compare:

"give me all the records equal to string '1000000'"
"give me all the records that match when compared to the integer 1000000"

The top one asks only for values matching that particular string. But what you've asked for is the bottom one.

The bottom one can't be optimised because it's not a 1:1 conversion - there are many strings that compare positively to the integer 1000000. So MySQL needs to go through all values to check each one if the comparison matches.

Strings that would match 1000000 in MySQL

There are lots

'1000000'
'1000000abcdef'
'1000000&**#&$('
'01000000'
'000001000000'
'+1000000'
'1000000.00000'
'1e6'
'00001.000e6abcdef'

As you can see, MySQL can't even use the index to narrow down the start of the string, because potential matches could contain characters before the first '1'.

Why doesn't MySQL change the way it handles this?

The way MySQL compares strings with numeric values is in line with the way it's documented and with other databases and scripting languages which compare strings and integers or convert strings to integers.

One thing that MySQL could have chosen to do differently is to disallow implicit conversion in this context, which would force the user to use the CAST built-in in the query - it could be argued this may prevent some accidents like this. However, it would also make what is a relatively common operation - comparison of a number with a number in a string - more verbose as a result.

At any rate, design decisions made in MySQL can't be reversed lightly if that would change behaviour of existing code.

Summary

In this case the user almost certainly intended to make the column a numeric column in which case the issues above wouldn't apply and the comparison would be easily satisfied by an index lookup.

Alternatively, they could have asked for a string to string comparison which would also have been relatively easily satisfied by an index lookup (with appropriate collation on the index).

But I've explained above why the comparison between two different types specified by the query they did write couldn't be satisfied with an index because there are multiple strings that would match that integer.

Why primary key has no good effect on select?

3 Answers3