… were made in the MySQL config: optimizer_switch=’index_condition_pushdown=off’ optimizer_switch=’mrr=on’ optimizer_switch=’mrr_sort_keys=on’ (only on MariaDB 5.5) optimizer_switch=’mrr_cost_based… 5.6 and MariaDB 5.5. Handler_mrr_rowid_refills counts how many times the buffer used by MRR had to…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one by one. So in the same way this blog post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
….5 FLUSH TABLES WITH READ LOCK does not work as optimally as you could think it works. Even though with general… | 10219 | root | localhost | dumptest | Query | 324 | Sending data | select count(*) from A,B | 0 | 0 | 2359297 | | 10290 | root | localhost | NULL…
Comment: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?
February 27, 2012 By 53 Comments
Here is a beautiful solution; SELECT COUNT(*) FROM (SELECT 1 FROM test WHERE some_conditions LIMIT any_no) alias_table; This results count of total rows wihtout any optimizations.
Comment: How much space does empty Innodb table take ?
April 3, 2012 By 7 Comments
… not counted as “free” until a 1MB extent (or whatever) is completely freed. This makes it difficult to know when to OPTIMIZE…
Post: A common problem when optimizing COUNT()
September 20, 2008 By 18 Comments
… what it’s trying to do. You can’t fully optimize a query unless you know how to consider alternative ways…) count the number of rows 2) count the number of values. Sometimes, but not always, these are the same thing. COUNT(*) always counts the number of rows in the result. If you write COUNT(col1) it counts the number of times…
Post: Using delayed JOIN to optimize count(*) and LIMIT queries
April 6, 2007 By 22 Comments
… (`id`) ) mysql> select count(*) from dim; +———-+ | count(*) | +———-+ | 30720 | +———-+ 1 row in set (0.00 sec) mysql> select count(*) from fact; +———-+ | count(*) | +———-+ | 7340032 | +———-+ 1 row in set (0.00 sec) mysql> select count(*) from fact where i select count(*) from fact…
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 12 Comments
… like query to illustrate some search is required, so count(*) can’t be optimized away for MyISAM Tables. To show percents for… | 290 | +——+—–+ 10 rows in set (21.12 sec) mysql> select count(*) cnt from dt where slack like “a%”; +———+ | cnt | +———+ | 2352996 | +———+ 1… the time is spent counting the rows which are already traversed and counted for group by operation. The obvious optimization is to get…
Post: Goal driven performance optimization
December 22, 2008 By Leave a Comment
…to focus only on Server Side optimization – the Client Side Optimization is also quite important in… and wtime>1 group by h; +——+———-+—————–+——————+ | h | count(*) | avg(wtime) | sphinx_ratio | +——+———-+—————–+——————+ | 00 | 5851 | 3….
Post: COUNT(*) vs COUNT(col)
April 10, 2007 By 41 Comments
…=latin1 mysql> select count(*) from fact; +———-+ | count(*) | +———-+ | 7340032 | +———-+ 1 row in set (0.00 sec) mysql> select count(val) from fact; +————+ | count(val) | +————+ | 7216582…. So COUNT(*) and COUNT(col) queries not only could have substantial performance performance differences but also ask different question. MySQL Optimizer does good…
