Using SELECT with a primary key in MySQL

Question

I'm trying to speed up my SELECT queries in my web application. I have done some pretty major optimizations, like ordering the rows returned by PHP rather than MySQL itself. Now I'm just wondering, is it faster to use the SELECT statement on columns that are a primary key?

Example, I have a table set up with the columns settingKey and settingValue, which I am using for my configuration. Would it be faster to grab the contents of settingValue when settingKey is a primary index, with a statement like this?

SELECT settingValue FROM config WHERE settingKey="MasterPassword"

I have tried Google'ing for the answer but came to no clear answer.

Thanks. :)

Answer 1

Yes, in cases where you select a single row from a large table, an primary key will almost certainly improve performance. This is because it will create an index on the table, which can be used to find the required row more efficiently than doing a full table scan.

Also, it will enforce uniqueness in your settingKey values.

Answer 2

I'm assuming there's no index at all on settingValue, partly because it wouldn't make sense and partly because you didn't mention it. If my assumptions are correct, querying by primary key is a tremendous performance gain; it is the most effective access path for the table.

As a rule of thumb, don't use a column that isn't indexed in your WHERE clause.

Answer 3

A primary key means :

• An index
• That's unique

Which means it'll be pretty good.

So, using a primary key in your where clause will probably be good for performances.

As a sidenote : it's often recommended to use columns that are "small" and "simple" for both primary keys and indexes :

• a "small" column, as in "only a couple of bytes", means simpler comparisons : it might be easier to compare two 2 bytes integers that it is to compare two 4 bytes integers.
• a "simple" column : it's easier/faster to compare two 4 bytes integers than it is to compare two varchar(20) -- think about collations : comparing strings often means case-insensitive and accents-insensitive comparison !

For instance, in your case, it would be (at least a bit) better for performances if your primary key was an integer field, and not a varchar.

Answer 4

Yes, a primary key will be quick as it's indexed and unique.

Alternatively you could create a new index on the fields you do want to query. See http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html for more details. This will give you about the same query performance as if you were querying on the primary key.

Caveat though is that it will increase database load times slightly.

Answer 5

SELECT settingValue FROM config WHERE settingKey=`MasterPassword`

With a MyISAM table, this query will have the same performance be the settingKey a PRIMARY KEY or a secondary index.

With an InnoDB table, this query will most probably be faster if settingKey is a secondary index.

InnoDB tables are clustered, this means that the table itself is the PRIMARY KEY index.

A table tends to be larger in size than a secondary index, so the lookups against the table will be slower than these against the index.