Working with MySQL Hints

A number of developers look at SQL tuning hints as a way to performance tune queries. Where the best way to tune SQL code is not to use hints. Hints add high maintenance code and try to influence the Cost Based Optimizer which can have negative impacts in the future. What if the data distributions change or the optimizer adds new features in the future? The best way to tune SQL code is to look at how the database server environment can be optimized so the optimizer can make the best decisions as possible. However, if you cannot get the optimizer to make the right decision a SQL hint may be required but it should usually be the choice of last resort.

Examples:
SELECT * FROM mytab IGNORE INDEX (col1_idx)
WHERE col1='A' AND col2='B';

SELECT * FROM mytab USE INDEX (col1_idx,col2_idx)
WHERE col1='A' AND col2='B' AND col3=3;

Here are some of MySQL SQL Tuning Hints:

• STRAIGHT_JOIN
• IGNORE INDEX [FOR JOIN]
• FORCE INDEX [FOR JOIN]
• USE INDEX [FOR JOIN]
• SQL_NO_CACHE
• SQL_CACHE
• The HIGH_PRIORITY
• The LOW_PRIORITY
• INSERT DELAYED
• INSERT LOW_PRIORITY
• INSERT DELAYED (MyISAM, MEMORY, and ARCHIVE tables)
• SQL_BUFFER_RESULT
• SQL_BIG_RESULT
• The SQL_BIG_RESULT
• SQL_SMALL_RESULT