…, 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
… (0.11 sec) mysql> create table j3 (c1 int); Query OK, 0 rows affected (0.10 sec) mysql> explain extended select j1.c1 from… plan: mysql> insert into j1 values (1); insert into j2 select * from j1; insert into j3 select * from j2; mysql> explain extended select j1… algorithm. For example: mysql> create view v1 as select * from j1; Query OK, 0 rows affected (0.10 sec) mysql> explain extended select * from…
Post: Extended EXPLAIN
July 24, 2006 By 9 Comments
… wish to tell us. It is best seen by example: mysql> explain extended select * from sbtest where id>5 and id>6 and… be converted just to id>6. Lets see another example: mysql> explain extended select t1.id,t2.pad from sbtest t1, sbtest t2… 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…
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…
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: EXPLAIN EXTENDED can tell you all kinds of interesting things
June 30, 2010 By 7 Comments
… may be used in an OUTER JOIN in newer version. mysql> EXPLAIN extended SELECT j1.c1 FROM j1, j2, j3 WHERE j1.c1… 1 row in set, 1 warning (0.01 sec) mysql> show warnings; +——-+——+—————————————————————————————————————————————————————————-+ | Level | Code | Message… 1 row in set (0.00 sec) mysql> select version(); +—————-+ | version() | +—————-+ | 5.0.26-max…
Post: COUNT(*) vs COUNT(col)
April 10, 2007 By 41 Comments
…; +————+ | count(val) | +————+ | 7216582 | +————+ 1 row in set (1.17 sec) mysql> select count(val2) from fact; +————-+ | count(val2) | +————-+ | 7340032 | +————-+ 1 row…: 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…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 93 Comments
… JOIN MySQL still will not be able to use it as Optimizer is not smart enough yet to detect such cases: mysql> explain… filesort. The solution for this problem is ether extending your indexes so MySQL Optimizer does not have to chose between better sort… does not use indexes, even if it is quite fast: mysql> explain select * from test order by k limit 5; +—-+————-+——-+——-+—————+——+———+——+———+——-+ | id | select…
