… with MySQL Optimizer I frequently run into is making poor decision when it comes to choosing between using index for ORDER BY or… * from goods where cat_id=5 and seller_id=1 order by price desc limit 10 \G *************************** 1. row *************************** id: 1 select…(cat_id) where cat_id=5 and seller_id=1 order by price desc limit 10 \G *************************** 1. row *************************** id: 1 select…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
… is MRR Key-ordered Scan. Let’s see what this optimization actually is. Key-ordered Scan for BKA With this optimization the idea of…’ and l_shipdate > ’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 see how effective are the join optimizations when…
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 14 Comments
… rows which are already traversed and counted for group by operation. The obvious optimization is to get rid of LIMIT 10 and… “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 together with ROLLUP. This does…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
… user_id > 2938575 AND parent_id=0 AND status=1 ORDER BY user_id LIMIT 1\G *************************** 1. row *************************** id: 1 select_type…() and suddenly start believing that they can always outsmart the optimizer by manually manipulating indexes in every query they write (not that…
Post: Is it query which needs to be optimized ?
March 6, 2007 By 9 Comments
… are couple of examples: GROUP BY Consider SELECT COUNT(*) cnt, page FROM log GROUP BY page ORDER BY cnt DESC limit 10, the query… as possible. ORDER BY I already wrote about ORDER BY LIMIT Optimization so I will not repeat it here. In the nutshell ORDER BY LIMIT can be… ORDER BY, these also can cause a lot of troubles. So designing schema make sure for queries you’re going to run ORDER BY…
Post: Thinking about running OPTIMIZE on your Innodb Table ? Stop!
December 9, 2010 By 18 Comments
…look at little benchmark I done by running OPTIMIZE for a second time on a … CHARSET=latin1 mysql> select * from a order by id limit 10; +—-+——————————————+ | id | c | +—-+——————————————+ | 1 | 813cf02d7d65de2639014dd1fb574d4c481ecac7 | …
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 14 Comments
… exactly happens during query execution – very helpful for MySQL Performance optimization: mysql> flush status; Query OK, 0 rows affected (0.00… people where age=18 order by last_online desc limit 10) UNION ALL (select * from people where age=19 order by last_online desc limit 10) UNION ALL (select * from people where age=20 order by last_online desc limit 10) ORDER BY last…
Post: MySQL Optimizer and Innodb Primary Key
October 3, 2006 By 9 Comments
… storage engine used to show difference: MySQL Optimizer correctly knows Innodb tables is clustered by primary key in the sense it would… filesort than to do lookups in primary key order: mysql> explain select * from innodb order by id \G *************************** 1. row *************************** id: 1 select… could skip filesort if ordering is done by primary key: mysql> explain select id from innodb where a=3 order by id \G *************************** 1…
Post: Four ways to optimize paginated displays
September 24, 2008 By 27 Comments
… the design pattern: display 20 results in some most-relevant order. Show a “next” and “previous” link. And usually, show how… typically see queries like this: select …. from … order by …. limit X, 20 If the ORDER BY can’t use an index (commonly the case… list. With that in mind, here are four suggestions for optimizing paginated displays that can give significantly better performance. On the…
Post: Optimizing slow web pages with mk-query-digest
April 7, 2011 By 14 Comments
… list me top slowest sessions: mk-query-digest –group-by=Thread_id –order-by=Query_time:sum in > out Spot on, the session… better if mk-query-digest would do a nested group-by and order-by within a group so I would avoid the extra… then even better than that would be if it would optimize the queries all together! Unfortunately mk-query-digest won’t…
