Query Processing Basic

Before tuning your slow query, you better understand how your database process your query first. Here are the steps:

1. Look up the query cache to see whether the resultset of your query is available. MySQL does this via hashing your query and use the hashed value to check for the results in the cache. MySQL uses the exact query text it receives, so the cache is sensitive to the most trivial variations like case, space and etc. Best practice: Have your application generates the queries.
2. Parse the query
3. Analysis the query
4. Optimize the query - Create the query plan that is believed to be the most efficient way to execute a query. The effort center around indexes and table join order. Use Explain to look into the query plan that database uses for your query.
5. Execution
6. Send the resultset back to client

How index impacts the performance

If you data does’t have sufficient diversity, database may choose to ignore your indexes and simply scan the entire table because it is faster to read data sequentially than locate rows using an index. The main reason index performance is worse in these circumstances goes all the way down to the hardware: disk seek performance. Indexes are always sorted, but the data on disk is not. Using an index means accessing the rows in index-sorted order (ie. random seek) rather than in the order they reside on the disk (ie. sequential access). The end result is more time spent moving around the disk and less time reading data. You can draw 2 conclusions form this knowledge:

1. If a table is going to remain very small, you may want to leave off the indexes.
2. Always use the production data for your test

Sometimes if you happen to write a query that requires only columns contained within a single query (multicolumn), mysql is smart enough to realize that all the required data is present in the index, so it doesn’t bother to fetch any of the rows from the disk.

Here is an example that worths your attention

SELECT * FROM weather WHERE city=’Hong Kong’ ORDER BY the_date DESC;

MySQL is weak in sorting. You are tempted to use index to sort it for you. However, odds are that you are already using city as index. And mysql uses at most one index per table per query. So, adding the_date index is not going to help b/c it may never be used. To resolve this, you can create multicolumn index (city, the_date). In this case, you will have an index mysql can use to quickly locate rows and an index that provides order to the data. That is awesome! One thing you need to pay attention is the order of the columns. The leftmost prefix rule dictates that city must appear first in the index to be used for that query. So, if you have a query based on the_date, it cannot be satisfied using the (city, the_date) index as the_date is not on the leftmost prefix in the index.

Other tricks

1.  Union instead of OR

Use HINT in MySQL

1. SQL_CACHE - cache resultset. But if query_cache_type = 1, this HINT has no effect as all select will be cached anyway.
2. SQL_NO_CACHE
3. STRAIGHT_JOIN
4. USE INDEX - suggest mysq to consider it instead of other indexes. However, mysql cannot decide not using it at all.
5. IGNORE INDEX
6. FORCE INDEX - force mysql to use it
7. SQL_BUFFER_RESULT - tell mysql the resultset is large, put it in temp table to free up any locks sooner.