…. row *************************** id: 1 select_type: SIMPLE table: info type: ref possible_keys: d key: d key_len: 3 ref: func rows: 17990 Extra: Using… the join so the info table just has bunch of ref lookups: mysql> select * from g; +—–+ | gr | +—–+ | 10 | | 20 | | 30 | | 40 | | 50…
Post: Possible optimization for sort_merge and UNION ORDER BY LIMIT
September 18, 2007 By 10 Comments
… UNION instead of single query works in this case: mysql> explain (select * from utest where c1=5) union (select…type: ref possible_keys: c1 key: c1 key_len: 4 ref: const rows: 108112 Extra: *************************** 2. row *************************** id: 2 select_type: UNION table: utest type: ref…
Post: Joining on range? Wrong!
May 17, 2010 By 10 Comments
…. row *************************** id: 1 select_type: SIMPLE table: t type: ref possible_keys: PRIMARY key: PRIMARY key_len: 98 ref: const rows: 1 Extra: Using… key_len: 8 ref: NULL rows: 1306 Extra: Using where; Using index In this case MySQL does not print ref at all, because…. row *************************** id: 1 select_type: SIMPLE table: t type: ref possible_keys: PRIMARY key: PRIMARY key_len: 98 ref: const rows: 1 Extra: Using…
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 14 Comments
…mysql> explain select * from people where age=18 order by last_online desc limit 10; +—-+————-+——–+——+—————+——+———+——-+——-+————-+ | id | select_type | table | type…
Post: MySQL VIEW as performance troublemaker
August 12, 2007 By 57 Comments
… mysql> explain select * from user_counts where user_id=5 \G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: type… id: 2 select_type: DERIVED table: comments type: index possible_keys: NULL key: PRIMARY key_len: 8 ref: NULL rows:…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 30 Comments
…ref: const rows: 1 Extra: *************************** 2. row *************************** id: 1 select_type: SIMPLE table: c type: ref possible_keys: state key: state key_len: 1 ref…performance, while join performance degraded dramatically. Here is why: mysql> explain select SQL_NO_CACHE c.city from …
Post: Be careful when joining on CONCAT
October 16, 2007 By 16 Comments
… too slow, the number of rows analyzed was really huge: mysql> EXPLAIN -> SELECT -> tb1.* -> FROM tb2 -> STRAIGHT_JOIN tb1 -> WHERE -> ( -> tb1…. row *************************** id: 1 select_type: SIMPLE table: tb2 type: ref possible_keys: gid key: gid key_len: 4 ref: const rows: 53 Extra: Using…
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… key_len: 18 ref: NULL rows: 99414 Extra: Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: t2 type: ref possible_keys: char…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… on IO bound workload, mentioned above. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ read_rnd_bufer_size=4M… execution plans. a. Bad Plan id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE nation ALL… Using where b. Good Plan id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE orders range…
Post: Covering index and prefix indexes
November 23, 2006 By 5 Comments
… prefixes: mysql> explain select k from t where i=5 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t type: ref possible_keys… column. mysql> explain select j,k from t where i=5 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t type: ref possible…
