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 — Jim Wright
– Jim Wright, Commented Jan 8, 2018 at 12:57

Rick James · Accepted Answer · 2018-01-08 16:19:24Z

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.

Nikhil Pareek · Accepted Answer · 2018-01-08 13:16:45Z

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 8, 2018 at 13:16

Nikhil Pareek

7642 gold badges9 silver badges25 bronze badges

3 Comments

Rick James Over a year ago

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".

Nikhil Pareek Over a year ago

I think the logic for which indexing to use is the same!

Rick James Over a year ago

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.

Collectives™ on Stack Overflow

MySQL index key on table with more columns

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related