Learn SQL Database Programming

Learning how to use index hints to improve queries

Generally speaking, MySQL will know how to choose the correct index for your queries. That being said, you can force it to choose which index to use with index hints on your query. USE INDEX keywords will allow you to specify the index you want your query to use. The following code block shows the syntax of an index hint:

SELECT columnsFROM tablename USE INDEX(indexname)WHERE condition;

To follow along with the USE INDEX query later in this section, add the following index to the appearances table:

USE lahmansbaseballdb; ALTER TABLE appearancesADD INDEX NC_playerid_g_cols (playerID ASC, G_all ASC, G_batting ASC, G_defense ASC) VISIBLE;

If you want to see a list of the indexes on your ...