CODEWITHSUNDEEP
mysqlsqlselect
LTzycLT
LTzycLT

Reputation: 509

Why primary key has no good effect on select?

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?

Upvotes: 1

Views: 223

Answers (3)

thomasrutter
thomasrutter

Reputation: 117353

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.

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239694

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.

Upvotes: 5

rogue-one
rogue-one

Reputation: 11587

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.

Upvotes: 0

Related Questions