… using index merge executes in 3.8 secs. I dont understand why MySQL uses index merge after a couple of mins when previously it was using a single index. I would greatly appreciate your answer. First explain —————– explain…_type | table | type | possible_keys | key | key_len | ref …
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… workload, mentioned above. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ read_rnd_…key. This is because of how status counter values are incremented when index lookup is performed. As I explained…. Bad Plan id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE nation …
Comment: What does Using filesort mean in MySQL?
March 15, 2012 By 27 Comments
… KEY (id), KEY (a), KEY (b) ) ENGINE=InnoDB Table 1 has 512000 randomly generated rows. This query uses index (type: index, key: a, key_len: 103, Extra: ”): EXPLAIN SELECT… Can someone EXPLAIN if there is a rule that MySQL uses to determine whether to use index or filesort? On mysql manual I notice…
Post: When EXPLAIN estimates can go wrong!
October 13, 2011 By 7 Comments
… possible_keys: type_created key: type_created key_len: 1 ref: const rows: 185440 Extra: Using index On 5.5: mysql [localhost] {msandbox} (foo2) > explain select count… possible_keys: type_created key: type_created key_len: 1 ref: NULL rows: 339184 Extra: Using where; Using index On 5.5: mysql [localhost] {msandbox} (foo2) > explain…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
…MySQL will still print full number. Here is example: mysql> explain select * from lt limit 10; +—-+————-+——-+——-+—————+———+———+——+——+————-+ | id | select_type | table | type | possible_keys | key | key_len…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 27 Comments
… it is not index prefix. It is important to look at key_len column in explain plan to see how many index parts are actually… MySQL is only reading index and not accessing rows you will see “using index” in EXPLAIN output. These are the main “core” use for indexes. You can also see others like using index for group…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
…explain select avg(length(val)) from idxtest ignore index (combined) where i1=50 and i2=50; +—-+————-+———+————-+—————+——-+———+——+——+————————————-+ | id | select_type | table | type | possible_keys | key | key_len…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 9 Comments
…mysql> explain select count(*) from idxitest where a=5 and b=5; +—-+————-+———-+——+—————+——+———+————-+——–+————-+ | id | select_type | table | type | possible_keys | key | key_len…
Post: MySQL VIEW as performance troublemaker
August 12, 2007 By 53 Comments
…: mysql> explain select * from user_counts where user_id=5 \G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: type: ALL possible_keys…: comments type: index possible_keys: NULL key: PRIMARY key_len: 8 ref: NULL rows: 1792695 Extra: Using index 2 rows in set (0.96 sec) mysql> select… which will JOIN 2 views just to see how indexes are used: mysql> explain select uc.cnt+uc2.cnt from user_counts uc…
Post: How (not) to find unused indexes
October 16, 2009 By 2 Comments
…mysql> EXPLAIN SELECT * FROM country WHERE population > 1000; # query 3 +—-+————-+———+——+—————+——+———+——+——+————-+ | id | select_type | table  | type | possible_keys | key | key_len…
