HI Peter I had a question regarding index merge (intersect). Below are two explains that I executed for the same SQL query… 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…
Comment: Benchmarking single-row insert performance on Amazon EC2
May 16, 2012 By 11 Comments
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… iibench is slightly incorrect. The correct URL for version you used is http://tokutek.com/downloads/iiBench-1.0.3.1…
Comment: What does Using filesort mean in MySQL?
March 15, 2012 By 27 Comments
… 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
… to the storage engine. The storage engine then uses the MRR interface (which I explained in my previous post) to lookup the rows in table2. So this means that we have traded many point index… because these have an impact on MRR performance, and BKA uses the MRR interface, so these buffers indirectly impact BKA performance…
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…
Comment: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
April 9, 2012 By 12 Comments
… one 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
… for explaining indexes is comparing them to index cards in an old library. In an old library, you used to (or still do) have index… a certain title in the library. Not using an index If you are not using the index cards, you would have to go shelf… its not using an index. Using an index You are interested in a book by J.R. Hartley. You go to the index card, you…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
… if using just single index and post filtering is any better: mysql [localhost] {msandbox} (test) > explain select avg(length(val)) from idxtest ignore index… choice but only to use combined index: mysql [localhost] {msandbox} (test) > explain select avg(length(val)) from idxtest ignore index(i1,i2) where i1…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 9 Comments
…Using where | +—-+————-+———-+——-+—————+———+———+——+——+————-+ 1 row in set (0.00 sec) # The plan also can look something like this: mysql> explain…| a | 4 | const | 1 | Using where; Using index; Using filesort | …
Post: Using index for ORDER BY vs restricting number of rows.
February 16, 2007 By 7 Comments
… rows: 296338 Extra: Using where; Using filesort 1 row in set (0.00 sec) mysql> explain select * from goods force index(cat_id) where… rows to display only few. If we force index as in second query explain will look scary with estimated million of rows…. Another interesting technique is to use sphinx search to accelerate sorting and retrieval which I should explain in details some time in…
