Oystein, As benchmark show using MRR makes sense when data is not in memory. Do you have any plans to store information about how much of the table fits in memory ? It can be helpful for other choices too, for example full table scan vs index scan depends a lot whenever table is in memory or not.
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… value fetched from step 1, and when the buffer is full sort them by pk_column, and do a range primary… index tuples could fit in the buffer. If this is > 0 then it means Handler_mrr_rowid_refills + 1 MRR range scans… the storage engine to perform the index record read and then to perform the MRR range scan on the PK. This causes…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 9 Comments
… original index it will quite likely be unable to use new index to full extent. What is solution ? You can have “redundant” indexes on… both (a) and (a,b) indexes and in the end decides to use neither rather doing full index scan until it finds a=100. This looks like an optimizer glitch in this case because it estimates it will scan…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
… index on the second column we get a full table scan: mysql [localhost] {msandbox} (test) > explain select avg(length(val)) from idxtest ignore index… of full table scan about 50 times more rows are scanned. This reflects very large performance difference between full table scan and access through the index…
Post: Multiple column index vs multiple indexes
August 22, 2008 By 13 Comments
…: Q1 will not use Index Merge technique for low cardinality table but instead pick to do single index scan. I’m not aware of the optimizer hint which would allow to force index merge as you can do with index… – Full table scan is performed and in this case turns to be faster than scanning 1/5th of the table using index. Also Full Table Scan…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 27 Comments
… such case you would see “Index” type in explain which correspond to scanning (potentially) complete table in the index order. It is very… able to use full index for sorting (note MySQL may not select to use index for sort if you sort full table without a… some cases – Indexes are sorted (at least on the page boundary) so doing index range scan you typically get many index entries from the…
Post: Indexes in MySQL
June 2, 2006 By 27 Comments
…% then a full table scan is preferable than an index access. As far as I know Oracle alway chooses a full table scan if selectivity… MySQL will use index for this query. Let’s compare the execution time with index access and with table scan: SELECT COUNT(SUBNAME… of Logical I/O for index access is less than for table scan. So be careful with indexes, they help in not all…
Comment: Extending Index for Innodb tables can hurt performance in a surprising way
June 1, 2010 By 9 Comments
…; As we know, the primary key is also the clustered index in InnoDB. If MySQL want to use the primary key… the id column in the clustered index, is this right? IMO, Mysql has to scan all the index leaves to find all records…. So in this situation, is there any difference between the full index scan and full table scan? Thanks.
Comment: InnoDB: look after fragmentation
November 5, 2009 By 12 Comments
… to start with. SELECT * FROM table should not be using index but rather primary key. The data is clustered by primary… generally faster. Also SELECT * FROM table can be only “using index” if there is a key which (together with primary key… lot less records to scan using one index than other, while of course if you’re doing full table scan or full index scan number of records…
Comment: Paul McCullagh answers your questions about PBXT
November 27, 2009 By 2 Comments
…. Which means that any full table scan is really going to be a full database scan, or alternately, a full index scan followed by nested lookups by… this mean the PBXT is going to have issues with full table scans and hence is probably not a good choice for…
