…SELECT non_key_column FROM tbl WHERE pk_column=val As you can see that the values returned from…It does not actually mean that queries with MRR are performing badly. The interesting thing is that though both MariaDB and MySQL…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a… (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take a look briefly at what this enhancement actually is, and what is it…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
…what the problem is. As of MySQL 5.5 FLUSH TABLES WITH READ LOCK does not work as optimally as you could think it… | dumptest | Query | 324 | Sending data | select count(*) from A,B | 0 | 0 | 2359297 | | …means single run away select can effectively cause downtime if you use backup solution which does…
Post: Computing 95 percentile in MySQL
November 28, 2008 By 5 Comments
…and have average response time of 1 second. What does it mean ? Really nothing – may be one page view was…if MySQL only provides you the avg: mysql> select count(*),avg(wtime) from performance_log_081128 where page_type=’search’; +———-+—————–+ | count(*) | avg(wtime) | …
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
…COUNT(*). mysql> select count(*) cnt from order_lines\G *************************** 1. row *************************** cnt: 155187034 1 row in set (32.03 sec) mysql> select sum(total_lines) cnt from…
Post: A common problem when optimizing COUNT()
September 20, 2008 By 18 Comments
… with COUNT(). This is database-agnostic, not related to MySQL. The problem is when the COUNT() contains a column name, like this: select count(col1) from… that meaning accurately. If the column is nullable, there’s a bug in your query — it won’t do what you think it does. You…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 139 Comments
…MySQL. If you design your data wisely considering what MySQL can do and what it…mysql> select count(pad) from large; +————+ | count(pad) | +————+ | 31457280 | +————+ 1 row in set (4 min 58.63 sec) mysql> select count(pad) from…
Post: MySQL Query Cache
July 27, 2006 By 69 Comments
…what MySQL Query Cache is – I’ve seen number of people being confused, thinking MySQL…select is not incremented. But if you have just 20% Cache hit rate does it mean it is not worth it ? Not really it…
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
…MySQL process: root@ubuntu:/var/log/mysql…select queries which is about 270us per select query. We can also get “BUSY TIME” here, subtracting CPU time from it…Count…what kind of hardware provides better balance of CPU vs IO utilization as well as as simple as how much does it…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
…MySQL does have its limitations, it reports only a subset of stats, however if you compare that to Percona server, it… SET timestamp=1325146286; select count(*) from auto_inc…means it accesses these pages in memory. What it says though is that, if this query would run on a cold MySQL instance, then it…
