Does the MySQL primary key affect the efficiency of a SELECT query?

Question

Does the primary key serve any purposes apart from uniquely identifying a specific row? For example, a table with an autoincremented primary INT key could greatly aid in search due to the option of perhaps a binary search; but does MySQL actually take advantage of this option?

If so, is the primary key required to be in order? And while we're at this question, if a table is in order of a particular column--whether primary or not--would that aid in the SELECT process if that particular column was specified in the WHERE clause?

If not, what are some of the precautions I could take in building a speed-efficient infrastructure for my database? For example, I have a table of articles where the first two columns are: an 'ID' as the primary key, and an alphanumeric ID for the URL. To show related articles, I have a table with two columns for relating article IDs. I have noticed that multi-table SELECT query is much more faster when the "related" table logs the primary key ('ID') rather than the alphanumeric ID (which is not primary nor numeric).

Answer 1

All things InooDB, the PRIMARY KEY has a major role. As ypercube mentioned towards end of answer, InnoDB uses a clustered key structure, and the key used for clustering is the PRIMARY KEY.

It is very important to have as small footprint for PRIMARY KEY as possible: the depth of the index is the minimum payment you have to pay so as to get to a leaf node. The depth of an InnoDB PRIMARY KEY is the starting payment for any index search.

My general advice is to use AUTO_INCREMENT primary keys for InnoDB. Here are three blog posts of mine which support this:

• Reasons to use AUTO_INCREMENT columns on InnoDB
• Simple guideline for choosing appropriate InnoDB PRIMARY KEYs
• Reducing locks by narrowing primary key

Answer 2

An INT column is 4 bytes. Without furtehr details about the alphanumericID, I assume it's a VARCHAR(40) (where 40 could be any number).

The related_articles table would probably have a PRIMARY KEY on (ID1, ID2) in one case (8-bytes wide) and on (alphaID1, alphaID2) in the other case (80-bytes wide). If the character set is UTF-8 and not Latin, it would be triple that (240-bytes wide) in the worst case (UTF-8 needs from 1 to 3 bytes per character).

And usually these tables have also an (ID2, ID1) index as well. So, we have 16 bytes per row for the indices on the first case and 160 bytes per row in the 2nd case (and up to 480 in the 3rd, bad case).

What this means is that any query that will use this (primary) index will be able to load 10 times more data (30 times if varchars are UTF) from the index on disk to the same amount of memory space. Since the disk is the slowest part, that also means about 10 times faster if a large part of the index is needed.

And because memory (RAM) is the most valuable for the database any amount that can be saved, means that larger indices and more of them can stay (cached) in memory and then used in subsequent queries, without the need of reloading from the (slow) disks.

(Performing joins on 4-byte columns is also more efficient than on 40-byte columns but that only would not explain a huge difference in performance.)

Also note that for InnoDB tables, the PRIMARY KEY (or the first UNIQUE if you haven't defined a primary) is chosen as the clustered key. And that its columns are added at all the other indices. So, the wider this clustered key is, the more space all other indices are bloated.

---

As an example, lets say that the article table has 5M rows and the related_articles has 30M rows. In the first case we'll need about half a GB for the indices of the related_articles. In the second case, that would be 5GB (and 15GB in the bad case).

Answer 3

For the database to be able to guarantee uniqueness of a row, it needs to search the table to make sure that the new incoming row has a unique key. This can only be practical and efficient, in large tables at least, when there is a structure called an index defined on that table that can be searched quicker than searching each row of the table in question. The same index could be used to locate rows by user queries. The index can be built on 1 or more columns of your choice, even on non-key columns to speed the search or to prevent duplicate rows.

Sometimes designers would use artificial keys such as auto-incremented keys of data type int for example, instead of long composite keys containing long strings of data. This increases the search speed but still adds an extra index that could affect the insert/update slightly. The shorter the key, the more keys could be loaded and searched in memory, hence improving the overall response time.

In your case, you could, use such an artificial key to enhance performance. To guarantee uniqueness, you will still need a unique index defined on your composite key.