Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL. Creating an index for "OR" queries

I have a table of 200k entries with columns of INT's. I want to create an index to make queries faster. This is the query I would like to execute: SELECT A,B,C,D,E FROM table WHERE A=23 and (B=45 or C=43). I created the following indexes: B, ACD, C, ABC.

With the EXPLAIN command I found that MySQL chooses the index ACD. So I kept populating the table with more values and I realized that MySQL was switching between the indexes above (not always the same one).

Since there are many inserts, having various indexes will cause performance issues and we can assume that this table is accessed by other queries that require different columns where every index makes sense.

I am aware of the USE INDEX(), but I would like to understand if we should trust MySQL to choose the right index.

like image 596
Daniel Vila Boa Avatar asked Mar 22 '12 20:03

Daniel Vila Boa


People also ask

Does MySQL use index for in query?

Introduction to MySQL indexesAn index is a data structure used to locate data without scanning all the rows in a table for a given query. Indexes help retrieve data faster. Indexes are not visible to the users. They help speed up queries that require a search.

How do I index a MySQL query?

You can create a simple index on a table. Just omit the UNIQUE keyword from the query to create a simple index. A Simple index allows duplicate values in a table. If you want to index the values in a column in a descending order, you can add the reserved word DESC after the column name.

How do I create an index in MySQL?

To create indexes, use the CREATE INDEX command: CREATE INDEX index_name ON table_name (column_name); You can an index on multiple columns.

What is the purpose of creating an index in MySQL table?

Indexes are used to retrieve data from the database more quickly than otherwise. The users cannot see the indexes, they are just used to speed up searches/queries. Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update).


1 Answers

Because of the OR in the SQL statement, MySQL is simply getting the first index that includes A, which is ACD.

I came to the conclusion that the way to solve this issue using an INDEX is to make two separate queries. SELECT A,B,C,D,E FROM table WHERE A=23 AND B=45 which will use the INDEX ABC and then SELECT A,B,C,D,E FROM table WHERE A=23 AND C=43 which will use INDEX ACD. This can all be done in one step with (...) UNION (...), which turns out to be quicker and only uses INDEX's.

like image 96
Daniel Vila Boa Avatar answered Sep 28 '22 18:09

Daniel Vila Boa