I think I can explain the performance you are seeing. But first I’d like … claim that this schema is not properly indexed. Here’s a theory that seems to explain the performance you are seeing. The… writing 4 times fewer B-tree values. The primary key and each secondary key each incur nearly the same cost, and the…
Comment: Multi Column indexes vs Index Merge
May 10, 2012 By 30 Comments
HI Peter I had a question regarding index merge (intersect). Below are two explains that I executed for the same SQL query… index merge after a couple of mins when previously it was using a single index. I would greatly appreciate your answer. First explain —————– explain… | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra…
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 * FROM table1 ORDER BY a LIMIT 1698, 1 This one uses filesort (Extra: Using filesort): EXPLAIN…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… at, in this post. Now let me briefly explain these optimizations. Batched Key Access Traditionally, MySQL always uses Nested Loop Join to… I explained in my previous post) to lookup the rows in table2. So this means that we have traded many point index… Key-ordered Scan for BKA here. Now there is one additional join optimization in MariaDB that I would like to quickly explain…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… Handler_read_key. This is because of how status counter values are incremented when index lookup is performed. As I explained at the start of the post that traditional index lookup (for non-index-only columns) involves, reading an index record, and…
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…
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…_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: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 9 Comments
…explain select count(*) from idxitest where a=5 and b=5; +—-+————-+———-+——+—————+——+———+————-+——–+————-+ | id | select_type | table | type | possible_keys | key | key…
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
… using further index key parts as soon as there is a range clause on the previous key part. So if you have INDEX(A… interesting case of EXPLAIN being wrong – it shows key_len=7 which corresponds to the full key while only first key part is used. Let us now replace the range with IN list in this query: mysql> explain…
Post: How (not) to find unused indexes
October 16, 2009 By 2 Comments
…(11) DEFAULT NULL, `status` enum(‘archived’,'active’) DEFAULT NULL, PRIMARY KEY (`id`), KEY `status` (`status`) ) ENGINE=MyISAM AUTO_INCREMENT=65691 DEFAULT CHARSET… find someway to parse and EXPLAIN all results, then subtract the indexes that were mentioned from all indexes known. There’s an…
