Index Hinting

If you do not specify any indexes for a table, MySQL needs to search every row in the table in order to return the results. This is known a full table scan. It is extremely undesirable for tables with a large row count such as products, users, or session information as it can take a long time to pull the data you need.

Indexes/Indices

I’m still learning a bit about how indexes work, but my high level understanding of them is that they create “sub-tables” (that’s my own way to think about them). In these sub-tables it has a column for the column being indexed that is ordered low to high and then a second column that contains a reference the main table rows. For instance:
People

ID First Last Age
1 Levi Jackson 25
2 Lindsay Webster 26
3 Ellie Webster 1
CREATE INDEX person_age ON People (Age);

The index would look like this in my head:

Age ID
1 3
25 1
26 2

 

So by creating indexes you make it easier to find results by age because it can do something like a binary search to find the row quicker. Normally the query optimizer will pick out the best index to use, but sometimes you may find yourself needing to pick the index you want the query to use, or making sure it DOES NOT use a certain index.

Use Index

Tell MySQL to use a particular index. The optimizer can still choose to do a full table scan if it deems that to be quicker than using the index.

SELECT * FROM people USE INDEX(person_age) WHERE ID > 3 AND age > 20;

This will end up doing a full table scan instead of using the person_age index as it is quicker.

Force Index

Tell MySQL to use a particular index, but to only fall back on doing a full table scan if it absolutely cannot use the index to return results.

SELECT * FROM people FORCE INDEX(person_age) WHERE ID > 3 AND age > 20;

Without the FORCE INDEX, it would use the primary key to find the results.

Ignore Index

Tell MySQL not to use a particular index.

SELECT * FROM people IGNORE INDEX(person_age) WHERE ID > 3 AND age > 20;

This will use just the primary key to find the results. Without the IGNORE INDEX it would have used the person_age index to narrow the result set down a bit.

 

Something I found to be interesting is that when comparing USE INDEX and FORCE INDEX, that the plain ol’ queries would have used the primary key by default, but that by using USE INDEX it was casting aside the possibility of using the primary key and instead was choosing between the key you specified and a full table scan. So using a USE INDEX may have quite the negative repercussions should the query optimizer find it is not an efficient key to use.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>