… comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one… post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… about the MRR optimization available in MySQL 5.6 here: http://dev.mysql.com/doc/refman/5.6/en/mrr-optimization.html and as… 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… in MySQL 5.6 code? As MRR was used in both MySQL 5.6 and MariaDB 5.5. Handler_mrr_rowid_refills counts…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… problem is. As of MySQL 5.5 FLUSH TABLES WITH READ LOCK does not work as optimally as you could think it… | 10219 | root | localhost | dumptest | Query | 324 | Sending data | select count(*) from A,B | 0 | 0 | 2359297 | | 10290 | root | localhost | NULL…
Post: Using delayed JOIN to optimize count(*) and LIMIT queries
April 6, 2007 By 22 Comments
…, PRIMARY KEY (`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…
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 12 Comments
… | 290 | | 6950 | 290 | +——+—–+ 10 rows in set (21.12 sec) mysql> select count(*) cnt from dt where slack like “a%”; +———+ | cnt | +———+ | 2352996… 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
… in many cases to focus only on Server Side optimization – the Client Side Optimization is also quite important in particular for aggressive…: mysql> select count(*),avg(wtime),avg(utime/wtime) cpu_ratio, avg(mysql_time/wtime) mysql_ratio ,avg(sphinx_time/wtime) sphinx_ratio, avg((wtime-mysql…
Post: MySQL VIEW as performance troublemaker
August 12, 2007 By 53 Comments
… assume MySQL would optimize your VIEWs same way as more advanced database systems would. Same as with subqueries and derived tables MySQL 5… of comments left by the given user ? mysql> select count(*) from comments where user_id=5; +———-+ | count(*) | +———-+ | 1818 | +———-+ 1 row in set (0… having things more modular and using MySQL VIEWs ? mysql> create view user_counts as select user_id,count(*) cnt from comments group by user…
Post: COUNT(*) vs COUNT(col)
April 10, 2007 By 41 Comments
… DEFAULT CHARSET=latin1 mysql> select count(*) from fact; +———-+ | count(*) | +———-+ | 7340032 | +———-+ 1 row in set (0.00 sec) mysql> select count(val) from fact; +————+ | count(val) | +————+ | 7216582… query. So COUNT(*) and COUNT(col) queries not only could have substantial performance performance differences but also ask different question. MySQL Optimizer does good…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…referring to the amazing MySQL manual, especially the option and variable reference table…..commanual Opened_tablesblogpercona.commanual optimizer_prune_levelblogpercona.commanual optimizer_search_depthblogpercona.commanual optimizer_switchblogpercona.commanual partitionblogpercona.commanual …
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… with COUNT(). This is database-agnostic, not related to MySQL. The problem is when the COUNT() contains a column name, like this: select count… the same thing. COUNT(*) always counts the number of rows in the result. If you write COUNT(col1) it counts the number of times…
