I think I can explain the performance you are seeing. But first I’d like … is to measure the cost of index maintenance. This experiment isn’t measuring the cost of index maintenance. Transaction-id is auto… claim that this schema is not properly indexed. Here’s a theory that seems to explain the performance you are seeing. 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…_ix | PRIMARY | 4 | const | 1 | Using filesort | | 1 | SIMPLE | LinksTbl2 | index_merge | tstamp,userid,converted_from_id,item_desc,treeid_ix…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… also look at, in this post. Now let me briefly explain these optimizations. Batched Key Access Traditionally, MySQL always uses Nested… I explained in my previous post) to lookup the rows in table2. So this means that we have traded many point index… algorithm only works with equi-joins. Now let me briefly explain how hash join algorithm works. Suppose you have two tables…
Comment: What does Using filesort mean in MySQL?
March 15, 2012 By 27 Comments
… 512000 randomly generated rows. This query uses index (type: index, key: a, key_len: 103, Extra: ”): EXPLAIN SELECT * FROM table1 ORDER BY a… 1699, 1 Can someone EXPLAIN if there is a rule that MySQL uses to determine whether to use index or filesort? On… index but it does not. I just see this note: With EXPLAIN SELECT … ORDER BY, you can check whether MySQL can use indexes…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… 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 then using the PK column value in the index record…
Comment: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
April 9, 2012 By 12 Comments
… of the columns in a composite index, it stops using the remaining parts of the index. At the link below you will…: http://jorgenloland.blogspot.com/2011/08/mysql-range-access-method-explained.html
Post: Explaining Indexes with a Library Metaphor
August 30, 2011 By 1 Comment
… explaining indexes is comparing them to index cards in an old library. In an old library, you used to (or still do) have index… title in the library. Not using an index If you are not using the index cards, you would have to go shelf… on the hard disk when its not using an index. Using an index You are interested in a book by J.R…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
… single column indexes (by hinting optimizer to ignore combined index) mysql [localhost] {msandbox} (test) > explain select avg(length(val)) from idxtest ignore index (combined… it would do the index merge: mysql [localhost] {msandbox} (test) > explain select avg(length(val)) from idxtest ignore index (combined) where i1=50…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
… ? Is it expected to rebuild index by sort of key_cache as it can for MyISAM. EXPLAIN may take long time In… | Using index | +—-+————-+——-+——-+—————+———+———+——+——+————-+ 1 row in set (0.00 sec) This statement obviously will not scan more than 10 rows but EXPLAIN shows…
Post: When EXPLAIN estimates can go wrong!
October 13, 2011 By 7 Comments
… of EXPLAIN. Now this can be a real pain as MySQL uses “the number of rows” estimation to pick and choose indexes… ref: const rows: 185440 Extra: Using index On 5.5: mysql [localhost] {msandbox} (foo2) > explain select count(*) from test_estimate where type… rows: 339184 Extra: Using where; Using index On 5.5: mysql [localhost] {msandbox} (foo2) > explain select count(*) from test_estimate where type…
