AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |
Back to Blog
Mysql optimizer hints1/24/2024 ![]() ![]() We will use the customers table from the sample database for the demonstration.įirst, use the SHOW INDEXES statement to display all indexes of the customers table: SHOW INDEXES FROM customers Notice that when you recommend the indexes to use, the query optimizer may either decide to use them or not depending on the query plan that it comes up with. In this syntax, the USE INDEX instructs the query optimizer to use one of the named indexes to find rows in the table. The following illustrates syntax of the MySQL USE INDEX hint: SELECT select_listĬode language: SQL (Structured Query Language) ( sql ) In addition, MySQL provides an alternative way that allows you to recommend the indexes that the query optimizer should by using an index hint called USE INDEX. To solve this issue, you should run the ANALYZE TABLE statement periodically to update the cardinality. ![]() The cardinality, however, may be not accurate for example in case the table has been modified heavily with many inserts or deletes. One of the most important parameters for choosing which index to use is stored key distribution which is also known as cardinality. To determine the best possible plan, the query optimizer makes use of many parameters. In MySQL, when you submit an SQL query, the query optimizer will try to make an optimal query execution plan. Summary: in this tutorial, you will learn how to use the MySQL USE INDEX hint instruct the query optimizer to use only a list of named indexes for a query.
0 Comments
Read More
Leave a Reply. |