… also look at, in this post. Now let me briefly explain these optimizations. Batched Key Access Traditionally, MySQL always uses Nested… BKA With this optimization the idea of MRR is further extended to improve join performance. As I told you above, when… algorithm only works with equi-joins. Now let me briefly explain how hash join algorithm works. Suppose you have two tables…
Post: Extended EXPLAIN
July 24, 2006 By 9 Comments
… shown by EXTENDED EXPLAIN for some reason, while it would be quite helpful. Finally lets look at third example: mysql> explain extended select * from…,…10) it completes in tiny fraction of the second. Anyway EXPLAIN EXTENDED is very valuable addition to EXPLAIN for MySQL Performance troubleshooting.
Post: EXPLAIN EXTENDED can tell you all kinds of interesting things
June 15, 2010 By 7 Comments
… familiar with the MySQL EXPLAIN command, fewer people are familiar with “extended explain” which was added in MySQL 4.1 EXPLAIN EXTENDED can show you…. EXPLAIN EXTENDED adds a warning message to the EXPLAIN output which displays additional information, including the rewritten query. To take a look at EXPLAIN EXTENDED…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 9 Comments
…; +———-+ | count(*) | +———-+ | 60434 | +———-+ 1 row in set (0.69 sec) mysql> explain select count(*) from idxitest where a=5 and b=5… in set (0.00 sec) The obvious optimization is to extend index from column (a) to column (a,b) right which…; +———-+ | count(*) | +———-+ | 60434 | +———-+ 1 row in set (0.02 sec) mysql> explain select count(*) from idxitest where a=5 and b=5…
Post: A workaround for the performance problems of TEMPTABLE views
May 19, 2010 By 7 Comments
… sec) We can use EXPLAIN EXTENDED to see that MySQL rewrites the view query to include the restriction: mysql> explain extended select c1, count… old one. We no longer examine millions of rows: mysql> explain select * from v2 where c1 = 10; +—-+————-+————+——–+—————+———+———+——-+——+————————–+ | id | select_type | table…
Post: COUNT(*) vs COUNT(col)
April 10, 2007 By 41 Comments
… few more queries: mysql> select count(*) from fact where i explain select count(*) from fact where i select count(val) from fact where i explain select count(val) from fact where i select count(val2) from fact where i explain select count(val2) from fact where i alter table fact…
Comment: When EXPLAIN estimates can go wrong!
October 15, 2011 By 7 Comments
…. Before executing the explain: [root@localhost ~]# mysqladmin –defaults-file=sandboxes/msb_vanilla_5_5_15/my.sandbox.cnf extended-status | grep -i… | After executing the explain: [root@localhost ~]# mysqladmin –defaults-file=sandboxes/msb_vanilla_5_5_15/my.sandbox.cnf extended-status | grep -i…
Comment: EXPLAIN EXTENDED can tell you all kinds of interesting things
June 17, 2010 By 7 Comments
Oh yes, I forgot to mention explain extended is very unstable and will crash server for certain queries.. put [ site:bugs.mysql.com "explain extended" crash ] into google for proof 
be careful!
Post: InnoDB: look after fragmentation
November 5, 2009 By 12 Comments
…_jumpy | 574 | +——————————+——-+ 2 rows in set (0.00 sec) and extended stats: # Query_time: 17.765369 Lock_time: 0.000137 Rows…
Comment: COUNT(*) vs COUNT(col)
April 19, 2007 By 41 Comments
… similar to count(0) as you cas se here: mysql> explain extended select count(*) from mytable; +—-+————-+——-+——+—————+——+———+——+——+——————————+ | id | select_type | table | type | possible… similar to count(1) as you can se here: mysql> explain extended select count(1) from mytable; +—-+————-+——-+——+—————+——+———+——+——+——————————+ | id | select_type | table | type…
