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: Multi Column indexes vs Index Merge
May 10, 2012 By 30 Comments
… the second SQL query when 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…
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… LIMIT 1699, 1 Can someone EXPLAIN if there is a rule that MySQL uses to determine whether to use index or filesort? On mysql… use an index but it does not. I just see this note: With EXPLAIN SELECT … ORDER BY, you can check whether MySQL can use indexes…
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: Using index for ORDER BY vs restricting number of rows.
February 16, 2007 By 7 Comments
… poor decision when it comes to choosing between using index for ORDER BY or using index for restriction. Consider we’re running web site… is possible to use index for further restriction and than using file sort, rather than using index for sorting and doing non-index based filtering for… rows: 296338 Extra: Using where; Using filesort 1 row in set (0.00 sec) mysql> explain select * from goods force index(cat_id) where…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 27 Comments
… MySQL uses single index – there are more complex rules of how indexes will be used if you look at multiple indexes usage with “index merge” Using Index to… 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 by…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
…Using intersect(i2,i1); Using where | +—-+————-+———+————-+—————-+——-+———+——+——+————————————-+ 1 row in set (0.00 sec) Hm… Optimizer decides to use index…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
… Extra | +—-+————-+——-+——-+—————+———+———+——+——+————-+ | 1 | SIMPLE | lt | index | NULL | PRIMARY | 4 | NULL | 1600 | Using index | +—-+————-+——-+——-+—————+———+———+——+——+————-+ 1 row in set (0.00 sec) This statement obviously will not scan more than 10 rows but EXPLAIN shows…
