… mysql prod instance . The first SQL query executed in 0.03 secs whereas the second SQL query when using index merge executes in 3.8 secs. I dont understand why MySQL…_from_id IS NULL OR LinksTbl2.converted_from_id=0) ORDER BY LinksTbl2.tstamp DESC, LinksTbl2.id DESC LIMIT 505…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… available in both MySQL 5.6 and MariaDB 5.5. You can read more about BKA in MySQL 5.6 here and BKA in MariaDB 5.5 here. However, MariaDB 5.5 has one additional optimization that is used in… > ’1995-03-09′ group by l_orderkey, o_orderdate, o_shippriority order by revenue desc, o_orderdate LIMIT 10; In-memory workload Now let’s…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…_nationkey group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by revenue desc LIMIT 20; In-memory workload Now let’s see how effective is MRR when the workload fits entirely in memory… and not in MySQL 5.6, is that because of a bug in MySQL 5.6 code? As MRR was used in both MySQL 5.6…
Comment: What does Using filesort mean in MySQL?
March 15, 2012 By 27 Comments
… looked at the “ORDER BY OPTIMIZATION” section in MySQL manual but could not find a reason why MySQL does not use index in the following scenario…): EXPLAIN SELECT * FROM table1 ORDER BY a LIMIT 1699, 1 Can someone EXPLAIN if there is a rule that MySQL uses to determine whether… SELECT … ORDER BY, you can check whether MySQL can use indexes to resolve the query. It cannot if you see Using filesort in the…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 93 Comments
…of MySQL Performance problems. Here is what you need to know about ORDER BY … LIMIT optimization to avoid these problems ORDER BY with LIMIT is most common use of ORDER BY in interactive applications …
Post: Possible optimization for sort_merge and UNION ORDER BY LIMIT
September 18, 2007 By 10 Comments
… rows in sorted order. Once this is implemented similar approach could be used for optimizing ORDER BY with IN. This original query (in memory … mysql> explain (select * from utest where c1=5 order by ord desc limit 10) union (select * from utest where c2=5 order by ord desc limit 10) order by…
Post: Implementing Parallel Replication in MySQL
November 8, 2010 By 3 Comments
… severe limitations in the MySQL server. We have a brief outline of the ideas at this wiki blueprint. So far, the “binlog order” idea… most negatively affected by the limitations, and whose problems can be improved the most with a (relatively) simple implementation. In particular, that means…
Post: Impact of the sort buffer size in MySQL
October 25, 2010 By 8 Comments
… allocates memory. Monty Taylor (here) have described the underlying issue in detail, but basically above 256kB the behavior changes and becomes… +%s.%N` OUT=`mysql -e “set session sort_buffer_size=32*1024*$i;select * from sorttest order by data limit 78000,1;show session… sorttest order by data limit 78000,1;select * from sorttest order by data limit 78000,1;select * from sorttest order by data limit 78000,1;select * from sorttest order by data limit…
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 12 Comments
…fast it is without order by (and limit): mysql> select grp, count(*) cnt from dt where slack like “a%” group by grp with rollup; +——-+———+ | grp…is considerable penalty associalted with GROUP BY WITH ROLLUP in MySQL – it is significantly slower than standard group by even though the only …
Post: 3 ways MySQL uses indexes
September 12, 2009 By 27 Comments
… (note MySQL may not select to use index for sort if you sort full table without a limit). However ORDER BY B or ORDER BY A… work A>5 ORDER BY B , A>5 ORDER BY A,B DESC or A IN (3,4) ORDER BY B – in these cases getting data in sorting form would require a bit more than simple range scan in the BTREE and MySQL decides…
