… workings of one of the nicest features of the newer MySQL optimizer: the Index Condition Pushdown Optimization, or ICP, which we… *************************** id: 1 select_type: SIMPLE table: cast_info type: ref possible_keys: role_id_note key: role_id_note key_len: 4 ref: const rows: 10259274 Extra…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
…mysql> explain select * from sbtest a,sbtest b where a.id=5 and b.id=a.k; +—-+————-+——-+——-+—————+———+———+——-+——+——-+ | id | select_type | table | type | possible_keys | key | key…
Post: Wow. My 6 year old MySQL Bug is finally fixed in MySQL 5.6
August 30, 2012 By 2 Comments
… really need is with 2147483647) mysql> explain select count(*) from trunc where i=4147483647; +—-+————-+——-+——+—————+——+———+——-+——-+————————–+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra…
Post: When EXPLAIN estimates can go wrong!
October 13, 2011 By 7 Comments
… select_type: SIMPLE table: test_estimate type: ref possible_keys: type_created key: type_created key_len: 1 ref: const rows: 185440 Extra: Using index On 5.5: mysql [localhost…: test_estimate type: range possible_keys: type_created key: type_created key_len: 1 ref: NULL rows: 339184 Extra: Using where; Using index On 5.5: mysql [localhost…
Post: MySQL Optimizer and Innodb Primary Key
October 3, 2006 By 9 Comments
…) mysql> explain select id from myisam where a=3 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: myisam type: ref possible_keys: a key: a key_len: 5 ref: const rows: 1 Extra: Using where 1 row in set (0.00 sec) MySQL However…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
…: Default config for MySQL 5.5 and MySQL 5.6, no tuning at all Config 2: MySQL 5.6 with all …_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 2704 Extra: Using where *************************** 2. row *************************** id: 1 select_type: SIMPLE table: lineorder type: ref possible_keys…
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 | possible_keys | key | key_len | ref | rows…_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +—-+————–+————–+——+—————+——+———+——-+——-+—————-+ | 1 | PRIMARY | people | ref | age | age | 1 | const | 12543 | Using where | | 2 | UNION | people | ref | age…
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… query execution plans. a. Bad Plan id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE nation ALL PRIMARY NULL….00 Using where b. Good Plan id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE orders range PRIMARY,i…
Post: MySQL Performance - eliminating ORDER BY function
October 17, 2007 By 6 Comments
…: tst type: ref possible_keys: i key: i key_len: 5 ref: const rows: 10 Extra: Using where 1 row in set (0.00 sec) mysql> explain…)) \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: tst type: ref possible_keys: i key: i key_len: 5 ref: const rows: 10 Extra: Using where…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
…id LIMIT 1; mysql> EXPLAIN SELECT user_id FROM users USE INDEX(user_type) WHERE user_type=2 AND user_id …*************************** 1. row *************************** id: 1 select_type: SIMPLE table: users type: ref possible_keys: user_type key: user_type key_len: 2 ref: const rows: 32404 Extra: …
