…explains that I executed for the same SQL query with a couple of mins gap on the same mysql… LIMIT 505 ; …
Post: Joining many tables in MySQL - optimizer_search_depth
April 20, 2012 By 6 Comments
…subqueries these might need to be executed during EXPLAIN phase yet making it unusable to check …found the following explanation from Timour Katchaounov in MySQL mailing list archives I have some recollection …picks value of min(number of tables, 7) essentially limiting search depth to no more than 7 …
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…, in this post. Now let me briefly explain these optimizations. Batched Key Access Traditionally, MySQL always uses Nested Loop Join to join… would like to quickly explain here, and that is Hash Joins. Hash Join As I have told before MySQL has only supported…, o_orderdate, o_shippriority order by revenue desc, o_orderdate LIMIT 10; In-memory workload Now let’s see how effective…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…_name, c_address, c_comment order by revenue desc LIMIT 20; In-memory workload Now let’s see …IO bound workload, mentioned above. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ read_rnd_bufer_…are incremented when index lookup is performed. As I explained at the start of the post that traditional index…
Comment: What does Using filesort mean in MySQL?
March 15, 2012 By 27 Comments
… 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 to use index or filesort? On mysql…
Comment: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
April 9, 2012 By 12 Comments
…repls, MySQL has a limitation on range conditions whereby as soon as it encounters a … very good explanation of MySQL range access and its limitations by Jørgen: http://jorgenloland.blogspot.com/2011/08/mysql-range-access-method-explained.html
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
… have LIMIT which restricts how many rows will be examined MySQL will still print full number. Here is example: mysql> explain select * from lt limit… to be used in particular sort is not seen in explain. Limited cost information Yes, number of rows to be scanned is… view. If you want to find more information about understanding EXPLAIN output, MySQL manual EXPLAIN page is good place to start.
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 93 Comments
…: mysql> explain select test.i from test, test t where test.k=5 and test.i=t.k order by t.k limit… indexes, even if it is quite fast: mysql> explain select * from test order by k limit 5; +—-+————-+——-+——-+—————+——+———+——+———+——-+ | id | select_type | table | type | possible…
Post: Possible optimization for sort_merge and UNION ORDER BY LIMIT
September 18, 2007 By 10 Comments
…=MyISAM DEFAULT CHARSET=latin1 mysql> explain select * from utest where c1=5 or c2=5 order by ord desc limit 10 \G *************************** 1… 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 rows from it: mysql> explain (select * from utest where c1=5 ) union (select * from utest where c2=5 ) limit 10 \G *************************** 1…
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 12 Comments
… and query becomes very slow: mysql> explain select * from people where age=18 order by last_online desc limit 10; +—-+————-+——–+——+—————+——+———+——-+——-+————-+ | id | select_type… avoid filesort of full table: mysql> explain (select * from people where age=18 order by last_online desc limit 10) UNION ALL (select…
