I vaguely recall a couple of blog posts recently asking something like “what’s the formula to compute mysqld’s worst-case maximum memory usage?” Various formulas are in wide use, but none of them is fully correct. Here’s why: you can’t write an equation for it.
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 [...]
MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 14 Comments
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | mysql> SELECT sql_no_cache name FROM people WHERE age in(18,19,20) AND zip IN (12345,12346, 12347); +----------------------------------+ | name | +----------------------------------+ | ed4481336eb9adca222fd404fa15658e | | 888ba838661aff00bbbce114a2a22423 | +----------------------------------+ 2 rows in set (0.00 sec) mysql> explain SELECT sql_no_cache name FROM people WHERE age in(18,19,20) AND zip IN (12345,12346, 12347); +----+-------------+--------+-------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | people | range | age | age | 4 | NULL | 9 | Using where | +----+-------------+--------+-------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) |
So as you see there are really different types of ranges in MySQL. IN range allows [...]
MySQL Query Cache
July 27, 2006 By 70 Comments
MySQL has a great feature called “Query Cache” which is quite helpful for MySQL Performance optimization tasks but there are number of things you need to know. First let me clarify what MySQL Query Cache is – I’ve seen number of people being confused, thinking MySQL Query Cache is the same as Oracle Query Cache [...]
