… results For the purpose of this benchmark, I have used TPC-H Query…query runs the optimizer would switch to using plan ‘a’, which does not involve MRR, and the query time for MySQL…query time goes down from ~11min to under a minute. The query time is reduced further when buffer size is set large…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…set to 6M and read_rnd_buffer_size set to 6M, the query time for MySQL 5.6 becomes approximately equal to that of MySQL…query #3, in fact disabling both of these provided the best result…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
…Query | 0 | NULL | show processlist | 0 | 0 | 1 | +——-+——+———–+———-+————+——+——————————+——————————+———–+—————+———–+ 5 rows in set (0.00 sec) As result…
Post: MySQL Query Cache
July 27, 2006 By 69 Comments
…MySQL Performance optimization tasks but there are number of things you need to know. First let me clarify what MySQL Query…Query Cache – meaning cache where execution plans are cached. MySQL Query Cache is not. It does not cache the plan but full result sets…
Post: Working with large data sets in MySQL
July 5, 2007 By 12 Comments
What does working with large data sets in mySQL teach you ? Of course you have to learn a lot about query optimization, art of building summary tables and tricks of executing queries…… this however results in the challenge as results you’ve gotten for small data set may not apply to large data set so you need…
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 12 Comments
…queries to see what exactly happens during query execution – very helpful for MySQL Performance optimization: mysql> flush status; Query OK, 0 rows affected (0.00 sec) mysql…
Post: Performance impact of complex queries
January 17, 2007 By 11 Comments
…MySQL Performance by complex queries on large data sets(ie some large aggregate queries) and batch jobs. It is not rare to see queries…would try to optimize throughput before latency, meaning if you have query which is doing … snapshot of current database with same results. In some cases you can’t …
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 93 Comments
…query log or by examining Hander statistics). If only 50 rows are examined to provide 10 rows of result set…MySQL still will not be able to use it as Optimizer is not smart enough yet to detect such cases: mysql…However if you’re dealing with LIMIT query with large offset efficiency will suffer. LIMIT…
Post: How fast can you sort data with MySQL ?
August 18, 2007 By 26 Comments
…MySQL can sort 1.000.000 rows, or rather return top 10 rows from sorted result set…optimal for given platform and given query (I did not test if it is the same for all platforms or data sets) – The optimal…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… that nested-loop joins on large data sets are very expensive. If MySQL supported sort-merge or hash joins…are not all that fast on big joins; the query optimizer can sometimes pick bad plans when you have … to it before I do!) you’ll see results on this blog. Another is Kickfire — also something …
