MySQL index key on table with more columns

Question

In my script, I have a lot of SQL WHERE clauses, e.g.:

SELECT * FROM cars WHERE active=1 AND model='A3';
SELECT * FROM cars WHERE active=1 AND year=2017;
SELECT * FROM cars WHERE active=1 AND brand='BMW';

I am using different SQL clauses on same table because I need different data.

I would like to set index key on table cars, but I am not sure how to do it. Should I set separate keys for each column (active, model, year, brand) or should I set keys for groups (active,model and active,year and active,brand)?

Possible duplicate of [Understanding multiple column indexes in MySQL query](https://stackoverflow.com/questions/12728832/understanding-multiple-column-indexes-in-mysql-query) — Jim Wright, Jan 08 '18 at 12:57

score 1 · Accepted Answer · answered Jan 08 '18 at 16:19

WHERE a=1 AND y='m'

is best handled by INDEX(a,y) in either order. The optimal set of indexes is several pairs like that. However, I do not recommend having more than a few indexes. Try to limit it to queries that users actually make.

INDEX(a,b,c,d):

WHERE a=1 AND b=22  -- Index is useful
WHERE a=1 AND d=44  -- Index is less useful

Only the "left column(s)" of an index are used. Hence the second case, uses a, but stops because b is not in the WHERE.

You might be tempted to also have (active, year, model). That combination works well for active AND year, active AND year AND model, but not active AND model (but no year).

More on creating indexes: http://mysql.rjweb.org/doc.php/index_cookbook_mysql

Since model implies a make, there is little use to put both of those in the same composite index.

year is not very selective, and users might want a range of years. These make it difficult to get an effective index on year.

How many rows will you have? If it is millions, we need to work harder to avoid performance problems. I'm leaning toward this, but only because the lookup would be more compact.

score 0 · Answer 2 · answered Jan 08 '18 at 13:16

0

We use single indexing when we want to query for just one column, same asin your case and multiple group indexing when we have multiple condition in the same where clause.

Go for single indexing.

For more detailed explanation, refer this article: https://www.sqlinthewild.co.za/index.php/2010/09/14/one-wide-index-or-multiple-narrow-indexes/

answered Jan 08 '18 at 13:16

Nikhil Pareek

734
9
24

Your reference is only partially relevant -- since it is aimed at SQL Server, not MySQL. There are a number of implementation details that lead to things such as: MySQL _almost never_ uses "index intersection". – Rick James Jan 08 '18 at 16:24
I think the logic for which indexing to use is the same! – Nikhil Pareek Jan 09 '18 at 05:32
1

Only partially! The structure of the data (BTree with clustered PK in MySQL) is different. The options (no hash, no bit, no ROWNUM in MySQL) are different. Etc. – Rick James Jan 09 '18 at 22:05

MySQL index key on table with more columns

2 Answers2