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
… you have the following query executed on the InnoDB table: SELECT non_key_column FROM tbl WHERE key_column…fetched from step 1, and when the buffer is full sort them by pk_column, and do a range… storage engine to perform the index record read and then to perform the MRR range scan on the PK. This…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 139 Comments
… in temporary table etc. Prefer full table scans to index accesses – For large data sets full table scans are often faster than range scans and other types of index lookups. Even if you look at 1% or rows or less full table scan…
Post: Indexes in MySQL
June 2, 2006 By 27 Comments
… 20% 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 over 25%. What with MySQL: mysql> EXPLAIN SELECT COUNT(SUBNAME) FROM t2 WHERE ID1=1; +—-+————-+——-+——+—————+——+———+——-+——-+————-+ | id | select_type | table…
Post: COUNT(*) for Innodb Tables
December 1, 2006 By 29 Comments
…) tables because they would simply read number of rows in the table from stored value. Innodb will however need to perform full table scan or full index scan because it does not have such counter, it also can’t be solved by simple singe counter for Innodb tables…
Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1
January 8, 2007 By 117 Comments
… size rows, auto increment primary key and couple of extra indexes. CREATE TABLE IF NOT EXISTS `$tableName` ( `id` int(10) unsigned NOT… key range lookup, same access types for primary key and full table scans. To highlight different properties of storage engines we tested ranges…: SELECT min(dob) FROM $tableName The hardest query performs a scan of all million rows. InnoDB is better than MyISAM by…
Post: How number of columns affects performance ?
September 28, 2009 By 10 Comments
… speed so I looked at full table scan in case data fits in OS cache completely. I created 3 tables – First containing single tinyint… index scan speed is very similar to full table scan speed – this is rather expected as table data is stored in BTREE index very similarly to how indexes…
Post: Multiple column index vs multiple indexes
August 22, 2008 By 13 Comments
… not – 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 is preferred for Q4 in all cases but in case of high cardinality multiple index…
Comment: Why MySQL could be slow with large tables ?
June 9, 2006 By 139 Comments
… mentioned it in the article but there is IGNORE INDEX() hint to force full table scan. There is no rule of thumb. As you… created range covering 1% of table was 6 times slower than full table scan… which means at about 0.2% table scan is preferable. In other… sorts indexes themselves and removers row fragmentation (all for MYISAM tables). It however can’t make row retrieval which is done by index…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 27 Comments
… read. For Full Table Scan operation difference often would be larger. It is also interesting to note performance of Innodb tables in this case… – traversing about same amount of rows full table scan performs about 25 times better than accessing rows via index (for the case when data…
