… result. Lets look at the following series of examples: CREATE TABLE `fact` ( `i` int(10) unsigned NOT NULL, `val` int(11… NULL values in it and so MySQL have to perform table scan to find out. This is also why result is different…. MySQL Optimizer does good job in this case doing full table scan only if it is needed because column can be NULL…
Comment: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 22, 2012 By 10 Comments
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.
Comment: Why MySQL could be slow with large tables ?
September 18, 2008 By 141 Comments
[...] range 1..100 selects about 1% of the table. The times for full table scan vs range scan by index: PLAIN TEXT [...]
Post: PBXT benchmarks
April 8, 2007 By 14 Comments
… tested only READ queries, similar to ones in benchmark InnoDB vs MyISAM vs Falcon (http://www.mysqlperformanceblog.com/2007/01/08/innodb… able to run benchmarks. Description of benchmark: We used table “normal” table structure which corresponds to typical structure you would see in… range lookup, same access types for primary key and full table scans. To highlight different properties of storage engines we tested ranges…
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 19 Comments
… is great as it can help to make full table scans and index scans more efficient. I can’t wait for ability to… batch jobs killing server performance overtaking buffer pool. Though full table scan is only one of replacement policy optimizations possible. Q8: How… a 3x performance boost and it would write at 100MBps vs 33MBps. Any plans to enable tuning of the checkpointing rate…
Comment: Full table scan vs full index scan performance
November 24, 2012 By 15 Comments
Sergei, Indeed Innodb table may be fragmented so full table scan will not be sequential, yet same is true for MyISAM which over time will get fragmented rows. Freshly created Innodb table… in primary index scan vs other index scan. The data is at least “clustered” in primary key so you will scan all data from…
Post: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
… difference of behavior of ANALYZE TABLE for MyISAM vs Innodb. I used the following simple table for tests: CREATE TABLE `antest` ( `i` int(10… ANALYZE table tool a little bit of time to run (even for this very small table) this is because ANALYZE does index scans to find number of exact values in the table. Now let us populate antest_innodb table…
Post: Using index for ORDER BY vs restricting number of rows.
February 16, 2007 By 8 Comments
… limit 10 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: goods type: ref possible_keys: cat_id,cat_id_2… limit 10 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: goods type: ref possible_keys: cat_id key: cat_id… not really selective it is likely it will need to scan less than 100 rows to generate result. The speed difference…
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
… 4.1.xx. PZ: I should note MySQL 5.0 vs 4.1 has a lot of performance differences and not…: We have a fairly large Innodb table (150GB) that is showing poor performance for full table scans. using O_DIRECT, we are seeing… 64 pages == 1 MB. Then a full table scan would be fast. Maybe the table has become fragmented? Or maybe you have not…
Comment: High-Performance Click Analysis with MySQL
December 23, 2008 By 13 Comments
… it affects replication or causes table locks if you happen to use MyISAM. 2) Real time vs delayed. I think for many…) MyISAM vs Innodb – if you have read only data you can often get data clustering with MyISAM too by ALTER TABLE … ORDER… use full table scan MERGE TABLES are very efficient. What is however often a bad choice to have huge single highly indexed table which…
