…, in this post. Now let me briefly explain these optimizations. Batched Key Access Traditionally, MySQL always uses Nested Loop Join to join… BKA With this optimization the idea of MRR is further extended to improve join performance. As I told you above, when… would like to quickly explain here, and that is Hash Joins. Hash Join As I have told before MySQL has only supported…
Post: EXPLAIN EXTENDED can tell you all kinds of interesting things
June 15, 2010 By 7 Comments
… with the MySQL EXPLAIN command, fewer people are familiar with “extended explain” which was added in MySQL 4.1 EXPLAIN EXTENDED can show you what the MySQL optimizer does to your query. You might not know this, but MySQL can…
Post: Extended EXPLAIN
July 24, 2006 By 9 Comments
… not 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: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 9 Comments
…=latin1 mysql> select count(*) from idxitest where a=5 and b=5; +———-+ | count(*) | +———-+ | 60434 | +———-+ 1 row in set (0.69 sec) mysql> explain… in set (0.00 sec) The obvious optimization is to extend index from column (a) to column (a,b) right which… mysql> select count(*) from idxitest where a=5 and b=5; +———-+ | count(*) | +———-+ | 60434 | +———-+ 1 row in set (0.02 sec) mysql> explain select…
Post: A workaround for the performance problems of TEMPTABLE views
May 19, 2010 By 7 Comments
….01 sec) We can use EXPLAIN EXTENDED to see that MySQL rewrites the view query to include the restriction: mysql> explain extended select c1, count(*) from…: mysql> select * from v2 where c1 = 100; Empty set (1.64 sec) You can see that MySQL is accessing millions of rows: mysql> explain…
Comment: COUNT(*) vs COUNT(col)
April 19, 2007 By 41 Comments
…(*) is similar to count(0) as you cas se here: mysql> explain extended select count(*) from mytable; +—-+————-+——-+——+—————+——+———+——+——+——————————+ | id | select_type | table | type | possible…) is similar to count(1) as you can se here: mysql> explain extended select count(1) from mytable; +—-+————-+——-+——+—————+——+———+——+——+——————————+ | id | select_type | table | type…
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: MySQL File System Fragmentation Benchmarks
March 21, 2008 By 28 Comments
… 100 1000 10000; do ./benchmark.php $i 10000000; mysql -e’drop database test1′; mysql -e’create database test1′; done; tables: 1; total… is close, the difference is perhaps explained by the fact files needed to be constantly extended (meta data updates) and reopened for…. A bit better performance in this case can be perhaps explained but a bit larger increment how tablespaces are allocated comparted…
Post: JOIN Performance & Charsets
October 21, 2008 By 2 Comments
… has 400,000. The data set fit easily in memory. mysql> EXPLAIN EXTENDED SELECT SQL_NO_CACHE COUNT(t1.char_id) > FROM t1… execution time of 3.12 seconds and had the following EXPLAIN: *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type…
Comment: Extended EXPLAIN
July 27, 2006 By 9 Comments
Peter, a small correction: EXPLAIN EXTENDED was introduced in 4.1, not in 5.0 (and it is documented at http://dev.mysql.com/doc/refman/4.1/en/explain.html)
