…_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
…, then with Key-ordered scan, the secondary key on col1 would be sorted by col1 i.e. in index order and then the lookup will be performed. So key-ordered scan is basically…_shipdate > ’1995-03-09′ group by l_orderkey, o_orderdate, o_shippriority order by revenue desc, o_orderdate LIMIT 10; In-memory workload Now…
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… 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…
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 … * 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… does not. I just see this note: With EXPLAIN SELECT … ORDER BY, you can check whether MySQL can use indexes to resolve…
Comment: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 6, 2012 By 2 Comments
…-l_discount)) as revenue, o_orderdate, o_shippriority from customer, orders, lineitem where c_mktsegment = ‘AUTOMOBILE’ and c_custkey = o_custkey…_orderkey and o_orderdate date ’1995-03-15′ group by l_orderkey, o_orderdate, o_shippriority order by revenue desc, o_orderdate limit 10;
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 93 Comments
… 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 with… will block index usage for order by. Sort by column in leading table if you have JOIN with ORDER BY … LIMIT you should try hard to…
Post: Possible optimization for sort_merge and UNION ORDER BY LIMIT
September 18, 2007 By 10 Comments
… together with order by efficiently. I’d say this is limitation as for this case you well could be retrieving sorted streams by the… add 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 ord desc limit 10 \G…
Post: Using index for ORDER BY vs restricting number of rows.
February 16, 2007 By 7 Comments
… goods where cat_id=5 and seller_id=1 order by price desc limit 10 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE…_id) where cat_id=5 and seller_id=1 order by price desc limit 10 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE…
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 12 Comments
…, count(*) cnt from dt where slack like “a%” group by grp order by cnt desc limit 10; +——+—–+ | grp | cnt | +——+—–+ | 9879 | 300 | | 3888 | 298 | | 1793… “a%” group by grp with rollup order by cnt desc limit 11; ERROR 1221 (HY000): Incorrect usage of CUBE/ROLLUP and ORDER BY Oops. Bad luck – for some reason you can’t use order by…
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
…. mysql> explain select count(*) cnt,c from gt group by c order by null limit 10; +—-+————-+——-+——+—————+——+———+——+———+—————–+ | id | select_type | table | type | possible_keys | key… performance mysql> select count(*) cnt,c from gt group by c order by null limit 10; +—–+——————————————+ | cnt | c | +—–+——————————————+ | 1 | 80c9a87595687ccb33fa525d396ee75658aec777 | | 1 | 05bdcc2c1bb051cff861cee1ffcb7f680922b13e | | 1…
