Best way to create Database index on MySQL

Question

I would like to search my MYSQL db table by combinations of multiple attributes, and I would like to index it. For example, if this is my table :

+----+--------+--------+--------+--------+
| id | field1 | field2 | field3 | field4 |
+----+--------+--------+--------+--------+
|    |        |        |        |        |

I want to run queries like these :

select * from table where field1=value1 and field2=value2;
select * from table where field3=value3 and field4=value4;
select * from table where field1=value1 and field2=value2 and field3=value3;
select * from table where field4=value4;

What is the best way to make an index for something like that?

CREATE INDEX my_index on table(field1, field2, field3, field4);

or something like :

CREATE INDEX my_index1 on table(field1); 
CREATE INDEX my_index2 on table(field2); 
CREATE INDEX my_index3 on table(field3); 
CREATE INDEX my_index4 on table(field4);

or something else entirely?

This question is very generic and is thus difficult to answer. As a general rule of thumb, you should index columns based on what queries you are going to run, how often you are going to run them and their impact on the workload. You might even add some columns for indexing purposes alone, with no semantic value. You should also take into account future changes to the database's and queries' structure. — Pyromonk
– Pyromonk, Commented Feb 21, 2019 at 10:35
"Index for each field i would say " @krishKM nope that would be called index shot gunning or index shotgun — Raymond Nijland
– Raymond Nijland, Commented Feb 21, 2019 at 10:40
To add to @Pyromonk index selectivity is also important you should know the Optimizer is costs based.. MySQL does not use a index if for example 90% from a table is needed or needs to be checked.. in fact using a index then would be more expensive then a full table scan..More random disk I/O requests vs one random disk I/O request and "stream" (read) the complete table and filter out the records which you don't need "using where" in a extra column in the EXPLAIN output will indicate this. — Raymond Nijland
– Raymond Nijland, Commented Feb 21, 2019 at 10:45
Is there any column that you use in every filter? Are there any that you don't use regularly? How about data entropy per column? — Nico Haase
– Nico Haase, Commented Feb 21, 2019 at 12:48

MIA · Accepted Answer · 2019-06-02 10:06:30Z

According to the type of queries you want to use, I would say the most recommended way to create the index is:

CREATE INDEX my_index1 on table(field1); 
CREATE INDEX my_index2 on table(field2); 
CREATE INDEX my_index3 on table(field3); 
CREATE INDEX my_index4 on table(field4);

Then you will create 4 different index that can be used independently, otherwise you will create a composite index.

Why one single index won't work here?

Because, with only one single index with multiple fields, your query only will apply the index if you are using the fields on the query from left to right strictly. I put some examples:

EXPLAIN SELECT * FROM table WHERE field2=value2 AND field1=value1;

This query, will apply the index for the fields field1, and field2. Why? Because you are using the two most left fields from the created index.

You can see it on the explain field possible_keys with the value my_index.

However the next example:

EXPLAIN SELECT * FROM table WHERE field3=value3 AND field4=value4;

Won't apply any index, because you are going directly to call methods from the most right.

You can see it on the explain field possible_keys with the value null.

And just as the last example:

EXPLAIN SELECT * FROM table WHERE field1=value1 AND field4=value4;

This query, only apply the index for field1, but not for field4. The reason? is not used the other field2 and field3 in between.

You can see it on the explain field possible_keys equal to my_index and field extras with the value Using index, using where.

You can find more information about composite index here:

http://www.mysqltutorial.org/mysql-index/mysql-composite-index/

Rick James · Accepted Answer · 2019-03-04 22:58:36Z

Let's start by creating the optimal index for each query:

select * from table where field1=value1 and field2=value2;
INDEX(value1, value2)  -- in either order

select * from table where field3=value3 and field4=value4;
INDEX(value3, value4)  -- in either order

select * from table where field1=value1 and field2=value2 and field3=value3;
INDEX(value1, value2, value3)  -- in any order

select * from table where field4=value4;
INDEX(value4)

Now, let's see if we can cut back on the number of indexes:

INDEX(value1, value2)  -- in either order, and
INDEX(value1, value2, value3)  -- in any order

can be combined as follows to have a single index that handles both selects well:

INDEX(value1, value2,   -- in either order
      value3)   -- afterwards

Similarly,

INDEX(value3, value4)  -- in either order
INDEX(value4)

-->

INDEX(value4, value3)  -- in THIS order

So, for those SELECTs, two composite indexes are optimal:

INDEX(value1, value2,   -- in either order
                      value3)   -- afterwards
INDEX(value4, value3)  -- in THIS order

But... Did you provide all the SELECTs? I suspect you did not. And you really allow all combinations of several columns. This gets really messy. Instead of going through the above exercise, I recommend you find the most likely combinations, build some composite indexes, then pare them back by noting that INDEX(a,b,c) is a pretty good replacement for INDEX(a,b,d).

But... It gets even worse if you don't only have =. When building a composite index, but the = column(s) first, then any INs, and finally no more than one 'range' test.

But... If you have OR instead of AND, well, forget about optimizing.

Rule of Thumb: Don't have more than about 5 indexes.

Now, if you want to start over, with real column names and real datatypes, that is, real clues of what makes sense, I can help you further.

Collectives™ on Stack Overflow

Best way to create Database index on MySQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related