… query with a couple of mins gap on the same mysql prod instance . The first SQL query executed in 0.03… merge executes in 3.8 secs. I dont understand why MySQL uses index merge after a couple of mins when previously…_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
…_shipdate > ’1995-03-09′ group by l_orderkey, o_orderdate, o_shippriority order by revenue desc, o_orderdate LIMIT 10; In-memory workload Now… (Hash Join and Key-ordered Scan disabled) You can see that the lowest query time is for MySQL 5.6 which takes… a look at the status counters. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ join_buffer_size=6M & read…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… key_column, pk_column FROM tbl WHERE key_column=x ORDER BY key_column (Note that secondary keys in InnoDB contain primary…_nationkey group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by revenue desc LIMIT 20; In… on IO bound workload, mentioned above. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ read_rnd_bufer_size=4M…
Comment: What does Using filesort mean in MySQL?
March 15, 2012 By 27 Comments
I looked at the “ORDER BY OPTIMIZATION” section in MySQL manual but could not find a reason why MySQL does not use index in… * FROM table1 ORDER BY a LIMIT 1698, 1 This one uses filesort (Extra: Using filesort): EXPLAIN SELECT * FROM table1 ORDER BY a LIMIT 1699, 1 Can someone EXPLAIN if there is a rule that MySQL uses to determine whether…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 93 Comments
Suboptimal ORDER BY implementation, especially together with LIMIT is often the cause 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…
Post: Possible optimization for sort_merge and UNION ORDER BY LIMIT
September 18, 2007 By 10 Comments
… ORDER BY ORD LIMIT 10 to individual queries. What if we do int manually ? 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: Using index for ORDER BY vs restricting number of rows.
February 16, 2007 By 7 Comments
… all combinations of restrictions and order by to be fully indexed. An extra problem comes from the fact MySQL prefers when it is…_id`,`seller_id` ) mysql> explain select * from goods where cat_id=5 and seller_id=1 order by price desc limit 10 \G *************************** 1… sec) mysql> explain select * from goods force index(cat_id) where cat_id=5 and seller_id=1 order by price desc limit 10…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
…order by total_price desc; ‘> ‘); Query OK, 1 row affected (0.00 sec) When the view is ‘enabled’, the contents are created: mysql…) -> JOIN demo.order_lines as ol USING (order_id) -> GROUP BY (customer_id) -> ORDER BY total_price desc -> LIMIT 10; +————-+————-+————-+ | …
Post: Impact of the sort buffer size in MySQL
October 25, 2010 By 8 Comments
… +%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
… run two queries: mysql> select grp, count(*) cnt from dt where slack like “a%” group by grp order by cnt desc limit 10; +——+—–+ | grp | cnt… 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…
