… 5.6 and MariaDB 5.5. Handler_mrr_rowid_refills counts how many times the buffer used by MRR had to… So during cold query runs the optimizer would switch to using plan ‘a’, which does not involve MRR, and the query time for… improvement in the optimizer, as it is clearly a part of the optimizer‘s job to select the best query execution plan. I…
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… | root | localhost | dumptest | Query | 324 | Sending data | select count(*) from A,B | 0 | 0 | 2359297 | | 10290 | root | localhost | NULL | Query | 317 | Waiting for… | root | localhost | dumptest | Query | 688 | Sending data | select count(*) from A,B | 0 | 0 | 2359297 | | 10290 | root | localhost | NULL | Query | 681 | Waiting for…
Post: A common problem when optimizing COUNT()
September 20, 2008 By 18 Comments
…’s another form for COUNT(): select count(distinct col1) from table; So when I see a query that just does COUNT(col1) I am left with these guesses: You meant to count the number of… them to reply and tell me what the query means so I can optimize it. A good coding standard can help here…
Post: Using delayed JOIN to optimize count(*) and LIMIT queries
April 6, 2007 By 22 Comments
… and which need to be joined to get query result. If you’re executing count(*) queries for such result sets MySQL will perform… makes queries with high offset values very expensive. To get better performance you can “Help” MySQL and remove JOIN for count(*) and do JOIN after limiting result set for retrieval queries. Lets look at following simple example…
Post: COUNT(*) vs COUNT(col)
April 10, 2007 By 41 Comments
… the second query. So COUNT(*) and COUNT(col) queries not only could have substantial performance performance differences but also ask different question. MySQL Optimizer does… queries: mysql> select count(*) from fact where i explain select count(*) from fact where i select count(val) from fact where i explain select count…
Post: Is it query which needs to be optimized ?
March 6, 2007 By 9 Comments
… from our customers regarding query optimization… which had one thing in common – It is not query which needed to be optimized. Way too frequently… index COUNT(*) query can cause a lot of troubles. Imagine for example some sort of profile browsing at social networking site – SELECT COUNT…
Post: Database access Optimization in Web Applications.
September 12, 2006 By 10 Comments
… not optimally indexed – this is easy. For example our ORDER BY rank query is such – adding index on rank column maxes this query to use 10 rows to return 10 rows – exactly what we’re looking for. However our COUNT(*) query is different… doing so by several queries, assuming this query would not need to analyze much more rows because it is optimized differently. One of…
Post: MySQL Query Cache
July 27, 2006 By 69 Comments
MySQL has a great feature called “Query Cache” which is quite helpful for MySQL Performance optimization tasks but there are number of… start of the query – Query Cache does simple optimization to check if query can be cached. As I mentioned only SELECT queries are cached – so… certain queries, which you know do not need to be cached. Counting query cache efficiency There are few ways you can look at query…
Post: Optimizing slow web pages with mk-query-digest
April 7, 2011 By 14 Comments
…# Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 519 # Exec time 2 148s 11us 33s …would optimize the queries all together! Unfortunately mk-query-digest won’t do that for you, but then there’s mk-query-advisor 
…
