… (0.11 sec) mysql> create table j3 (c1 int); Query OK, 0 rows affected (0.10 sec) mysql> explain extended select j1.c1 from….10 sec) mysql> explain extended select * from v1 where c1=1; +—-+————-+——-+——+—————+——+———+——+——+————-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra…
Post: Extended EXPLAIN
July 24, 2006 By 9 Comments
…EXTENDED EXPLAIN for some reason, while it would be quite helpful. Finally lets look at third example: mysql> explain extended…ref | rows | Extra | +—-+——————–+——–+—————–+—————+———+———+——+———+————————–+ | 1 | PRIMARY | …
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 9 Comments
…mysql> select count(*) from idxitest where a=5 and b=5; +———-+ | count(*) | +———-+ | 60434 | +———-+ 1 row in set (0.69 sec) mysql> explain…extend…Extra | +—-+————-+———-+——+—————+——+———+——-+——+——————————————+ …
Post: A workaround for the performance problems of TEMPTABLE views
May 19, 2010 By 7 Comments
…EXPLAIN EXTENDED to see that MySQL rewrites the view query to include the restriction: mysql> explain extended…
Comment: COUNT(*) vs COUNT(col)
April 19, 2007 By 41 Comments
… you cas se here: mysql> explain extended select count(*) from mytable; +—-+————-+——-+——+—————+——+———+——+——+——————————+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra… can se here: mysql> explain extended select count(1) from mytable; +—-+————-+——-+——+—————+——+———+——+——+——————————+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra…
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…: PRIMARY key: PRIMARY key_len: 18 ref: NULL rows: 99414 Extra: Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table…
Comment: EXPLAIN EXTENDED can tell you all kinds of interesting things
June 30, 2010 By 7 Comments
…JOIN in newer version. mysql> EXPLAIN extended SELECT j1.c1 FROM j1…possible_keys | key | key_len | ref | rows | Extra | +—-+————-+——-+——+—————+——+———+——+——+—————————————————–+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | …
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
… is ether extending your indexes so MySQL Optimizer does not…mysql> explain select * from test order by k limit 5; +—-+————-+——-+——-+—————+——+———+——+———+——-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra…
Post: InnoDB: look after fragmentation
November 5, 2009 By 12 Comments
… +——————————+——-+ 2 rows in set (0.00 sec) and extended stats: # Query_time: 17.765369 Lock_time…5903053 | +—————————+ 1 row in set (2 min 8.92 sec) mysql> SHOW STATUS LIKE ‘Innodb_scan_pages%’; +——————————+——-+ | Variable_name | Value…
