In my post on estimating query completion time, I wrote about how I measured the performance on a join between a few tables in a typical star schema data warehousing scenario. In short, a query that could take several days to run with one join order takes an hour with another, and the optimizer chose [...]
MySQL Performance – eliminating ORDER BY function
October 17, 2007 By 6 Comments
One of the first rules you would learn about MySQL Performance Optimization is to avoid using functions when comparing constants or order by. Ie use indexed_col=N is good. function(indexed_col)=N is bad because MySQL Typically will be unable to use index on the column even if function is very simple such as arithmetic operation. Same can [...]
Possible optimization for sort_merge and UNION ORDER BY LIMIT
September 18, 2007 By 10 Comments
Every so often you need to perform sort results retrieved from MySQL when your WHERE clause goes beyound col=const values which would allow MySQL to still use second portion of the index for the order by. Ranges as well as IN lists make this optimization impossible, not even speaking about index merge optimization. Lets look [...]
Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 14 Comments
Quite typical query for reporting applications is to find top X values. If you analyze Web Site logs you would look at most popular web pages or search engine keywords which bring you most of the traffic. If you’re looking at ecommerce reporting you may be interested in best selling product or top sales people. [...]
To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?
August 28, 2007 By 57 Comments
When we optimize clients’ SQL queries I pretty often see a queries with SQL_CALC_FOUND_ROWS option used. Many people think, that it is faster to use this option than run two separate queries: one – to get a result set, another – to count total number of rows. In this post I’ll try to check, is [...]
Is it query which needs to be optimized ?
March 6, 2007 By 9 Comments
Last few days I had a lot of a lot of questions at MySQL Performance Forum as well as from our customers regarding query optimization… which had one thing in common – It is not query which needed to be optimized. Way too frequently people design schema first and then think how the queries they [...]
Using index for ORDER BY vs restricting number of rows.
February 16, 2007 By 8 Comments
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 there [...]
MySQL Optimizer and Innodb Primary Key
October 3, 2006 By 9 Comments
Innodb primary key is special in many senses and I was always wondering how well MySQL is integrated with Innodb to take advantage of these special features. Lets see which things work and which things do not:
Database access Optimization in Web Applications.
September 12, 2006 By 10 Comments
This is pretty simple approach I often use called to optimize web application performance if problem happens with few pages. If we have “everything is slow” problem looking at slow query logs may be better start. So what could you do ? Look at the information shown on the page which comes from database. This [...]
ORDER BY … LIMIT Performance Optimization
September 1, 2006 By 100 Comments
Suboptimal ORDER BY implementation, especially together with LIMIT is often the cause of MySQL Performance problems. Here is what you need to know about ORDER BY … LIMIT optimization to avoid these problems ORDER BY with LIMIT is most common use of ORDER BY in interactive applications with large data sets being sorted. On many [...]
