How can you tell if the index is being used or not? How would this information help in optimizing or tuning a database(s)?
MySQL is performing an expensive table scan on package_header every time a user searches on recipient fax. Considering the sheer size of the table, it’s apparent that this leads to very lengthy queries. It also explains the sporadic nature of the query problem: Most status queries use some other lookup criteria.
When you interview the developer of the query, you learn that this query exists to serve customers, who might not always know the area code for the recipient fax. To make the query more convenient, the developer allowed users to just provide a phone number, and he places a wildcard before and after the number to find all possible matches. He’s aghast to learn that this type of query frequently renders existing indexes useless.
How would the EXPLAIN and ANALYZE command help when troubleshooting this matter?
Problem Queries
After looking into the query situation, you realize that, basically, two types of problem queries exist. The first is encountered when a user tries to look up the status of a shipment. However, it is not consistent: It appears to happen sporadically for most users. The second problem query happens to everyone whenever they attempt to accept a new package for shipment.Package Status Lookup
Internal employees and external website users have begun complaining that it takes too long to look up the shipping status for a package. What makes this more perplexing is that it doesn’t happen all the time. Some queries run very fast, whereas others can take minutes to complete. Now that you’ve found what appears to be a problem query, your next step is to run EXPLAIN to see what steps the MySQL optimizer is following to obtain results.MySQL is performing an expensive table scan on package_header every time a user searches on recipient fax. Considering the sheer size of the table, it’s apparent that this leads to very lengthy queries. It also explains the sporadic nature of the query problem: Most status queries use some other lookup criteria.
When you interview the developer of the query, you learn that this query exists to serve customers, who might not always know the area code for the recipient fax. To make the query more convenient, the developer allowed users to just provide a phone number, and he places a wildcard before and after the number to find all possible matches. He’s aghast to learn that this type of query frequently renders existing indexes useless.
How would the EXPLAIN and ANALYZE command help when troubleshooting this matter?
Comments
Post a Comment