… optimizations which we will also look at, in this post. Now let me briefly explain these optimizations. Batched Key Access Traditionally, MySQL always uses Nested Loop Join to join two or more tables…
Post: Joining many tables in MySQL - optimizer_search_depth
April 20, 2012 By 6 Comments
… might need to be executed during EXPLAIN phase yet making it unusable to check the optimizer performance. Solution for this problem was to use set optimizer_search_depth=0, rarely used option…
Comment: What does Using filesort mean in MySQL?
March 15, 2012 By 27 Comments
… the “ORDER BY OPTIMIZATION” section in MySQL manual but could not find a reason why MySQL does not use index in the…: 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 to use…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… do not belong to the secondary index definition (and hence covering index optimization is not used), then primary key lookups have to be performed… values are incremented when index lookup is performed. As I explained at the start of the post that traditional index lookup….orders.o_orderkey 2 100.00 Using where So during cold query runs the optimizer would switch to using plan ‘a’, which does not…
Post: Diamond Keynote Panel, BOFs, Lightning Talks, and McAfee and AOL Sponsorships
April 2, 2012 By 1 Comment
…or topic interests to enjoy some quality face time. Topics include “Query Optimization”, “MySQL and Ruby … opportunities during which attendees will propose, explain, exhort, and rant on a variety …you haven’t registered but plan to attend, register now using discount code PerLiveSC and receive…
Post: Four ways to optimize paginated displays
September 24, 2008 By 26 Comments
…) execute a separate SELECT to count the rows. There are ways to optimize so you don’t have to do quite so much offsetting…’s not that easy to do in the real world; you can usually optimize for one access method to the data at some… way to avoid some of the costs. Estimate how many results there are. Again, Google does this and nobody complains. Use EXPLAIN and…
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
… of EXPLAIN being wrong – it shows key_len=7 which corresponds to the full key while only first key part is used. Let… see it does not work while I know I used exactly this trick to optimize some nasty queries. It looks like equality propagation… – exactly what we tried to avoid. It is easy to block equality propagation by using some trivial function: mysql> explain select sum(events) from…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 93 Comments
… still will not be able to use it as Optimizer is not smart enough yet to detect such cases: mysql> explain select test.i from… indexes could be used on each of the tables to optimize order by, or at least local sort could be used for each t… lookup or use FORCE INDEX to force it to use appropriate index. One more note about ORDER BY … LIMIT is – it provides scary explain statements…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
… Derived tables (selects in FROM clause) are used. MySQL will execute these subselects on explain to provide plan for top most statement, so… also not used in optimization which may produce suboptimal plans if stored functions are used in queries. In general in my opinion EXPLAIN needs…
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 12 Comments
… range allows to optimize lookups on the second key part, while BETWEEN and other ranges do not. Using same access type in EXPLAIN makes… | Using where; Using filesort | +—-+————-+——–+——-+—————+——+———+——+——-+—————————–+ 1 row in set (0.00 sec) We can however use UNION to avoid filesort of full table: mysql> explain (select…
