Tag - Optimizer

Using index for ORDER BY vs restricting number of rows.

One interesting problem with MySQL Optimizer I frequently run into is making poor decision when it comes to choosing between using index for ORDER BY or using index for restriction.
Consider we’re running web site which sell goods, goods may be from different categories, different sellers different locations which can be filtered on, and […]

Read more

MySQL ORDER BY LIMIT Performance Optimization

Suboptimal MySQL ORDER BY  implementation, especially together with LIMIT is often the cause of MySQL performance problems.
Here is what you need to know about MySQL ORDER BY LIMIT optimization to avoid these problems.
MySQL ORDER BY with LIMIT is most common use of ORDER BY in interactive applications with large data sets being sorted. On many […]

Read more

MySQL: Followup on UNION for query optimization, Query profiling

Few days ago I wrote an article about using UNION to implement loose index scan.
First I should mention double IN also works same way so you do not have to use the union. So changing query to:

Shell

mysql> SELECT sql_no_cache name FROM people WHERE age in(18,19,20) AND zip IN (12345,12346, 12347);
+———————————-+
| […]

Read more