Phonexay Singharatsavong

Phonexay Singharatsavong

Who am I?

I have been programming for over 20 years and wanted to start a blog about things I have learned over the years, am still learning, and will be learning, feel free to contact me.

What I write about

Recent Posts

MySQL Index Hinting

Today we'll explore mysql index hinting. I've come across this a few times in my life programming. Mostly due to using a table with a lot of indexes that were created on it, especially in a team setting, where multiple developers added their own indexes. This will give the query optimizer issues on figuring out which indexes to use, so this can be helpful, but the overall and better solution is to clean up the indexes or make them more efficent, saving memory and space. Index hinting only works with 'SELECT' and 'UPDATE' query statements.

There are a few types of mysql index hinting, but I'll go through the main three that I believe can be useful: 'USE', 'IGNORE', and 'FORCE'. I'll go through all three of them on a high level.

The 'USE' index is the one I have used the most. You can use this to give the query optimizer a hint on which indexes it should be using, keep in mind, I have had issues where it does ignore this. If you are curious to know which index is being use run your query with 'EXPLAIN' and this will show you which index is being used by your query. Remember only one index can be use, this is why it is important to design your table indexes efficiently. Here is an example of the 'USE' syntax: 

SELECT * FROM table1 USE INDEX (col1_index)
  WHERE col1=1 AND col2=2 AND col3=3;

You can have multiple indexes in the hinting, but only one will be used.

The next type of index hinting is the 'IGNORE'. As the name implies this will tell a query to 'IGNORE' an index or indexes instead of using it. Here is an example: 

SELECT * FROM table1 IGNORE INDEX (col3_index)
  WHERE col1=1 AND col2=2 AND col3=3;

The final type of index hinting is the 'FORCE', it is like the 'USE', but with 'FORCE' you will tell the query optimizer to use this index, instead of it trying it going through all the indexes. Sometimes using 'USE' the query optimizer might still use another index, but 'FORCE' will tell it to use that index. Here is an example: 

SELECT * FROM table_name FORCE INDEX (index_list)WHERE condition;

There are a few other MySQL Index hinting that were introduce in MySQL 8, but these three are the ones I have used and have proven helpful, when I can't redo indexes on a table.