MySql using primary index instead of multiple column one!

Question

Ok so I've a SQL query here:

 SELECT a.id,... FROM article AS a WHERE a.type=1 AND a.id=3765 ORDER BY a.datetime DESC LIMIT 1

I wanted to get exact article by country and id and created for that index with two columns type and id. Id is also primary key. I used the EXPLAIN keyword to see which index is used and instead of the multiple column index it used primary key index, but I did set the where stuff exactly in order as the index is created.

Does MySQL use the primary key index instead of the multiple column index because the primary one is faster? Or should I force MySql to use the multiple column index?

P.S. Just noticed it was stupid to use order when there is 1 result row. Haha. It increased the search time for 0.0001 seconds. :P

Answer 1

I don'e KNOW, but I would THINK that the primary key index would be the fastest available. And if it is, there's not much use using any other index. You're either going to have a article with an id of 3765 or you're not. Scanning that single row to determine if the type matches is trivial.

If you're only returning one row, there's no point to your ORDER BY clause. And the only point to the a.type=1 is to reject an article with the right id if the type is not correct.

Answer 2

The primary key is unique, so there's no need for MySQL to check any other index. a.id=3765 guarantees that there will be no more than one row returned. If a.type=1 is false for that row, then nothing will be returned.

Answer 3

MySQL allows for up to 32 indexes for each table, and each index can incorporate up to 16 columns. A multiple-column / composite index is considered a sorted array containing values that are created by concatenating the values of the indexed columns. MySQL uses multiple-column indexes in such a way that queries are fast when you specify a known quantity for the first column of the index in a WHERE clause, even if you do not specify values for the other columns.

If you look very carefully in how MySQL uses indexes, you will find that indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows.

In MySQL, a primary key column is automatically indexed for efficiency, as they use the in-built AUTO_INCREMENT feature of MySQL. On the other hand, one should not go overboard with indexing. While it does improve the speed of reading from databases, it slows down the process of altering data in a database (because the changes need to be recorded in the index). Indexes are best used on columns:-

• that are frequently used in the WHERE part of a query
• that are frequently used in an ORDER BY part of a query
• that have many different values (columns with numerous repeating values ought not to be indexed).

So I try to use the primary key if my queries can suffice its use. When & only when it is required for more such indexing & fastness of fetching records, do I use the composite indexes.

Hope it helps.