…) DEFAULT ” NOT NULL, PRIMARY KEY (id) ) CREATE INDEX k on sbtest$I(k) and tables…,10000) // distributed by pareto distribution $C, $PAD = random_string() BEGIN INSERT INTO sbtest (id, k, c…MyISAM Specific options key_buffer_size = 8M read_buffer_size = 1M read_rnd_buffer_size = 4M bulk_insert_buffer…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… the secondary index definition can result in a lot of Random I/O. The purpose of MRR is to reduce this Random I… 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 the PK column value in the index record…
Post: The MySQL optimizer, the OS cache, and sequential versus random I/O
April 28, 2008 By 17 Comments
… cost for the MySQL query optimizer is a single random 4k data page read. In general, it’s a pretty good metric… thought: It’s performing index lookups into the fact table, which is big. An index lookup is a random I/O. A modern disk can do about 100 random I/O’s per…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
… all indexes are created equal. Some indexes may be placed in sorted way or pages placed in random places – this may affect index scan/range scan speed dramatically. The rows referenced by indexes also could be… table and it will be completely random. We’ll need to perform 30 millions of random row reads, which gives us 300.000…
Post: Full table scan vs full index scan performance
November 23, 2012 By 15 Comments
… do a full index scan (fast sequential reads, no problem) and then lots of random reads to fetch rows by index value. And random reads are orders of magnitude slower than sequential reads…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
… increases, as there are going to be a lot more random index lookups needed to be performed because of the increase in….5 prefer to do an index range scan on the secondary key o_orderdate which results in random scans of the PK… orders in PK order has another benefit that it implies reading the table lineitem in PK order. Hence, these benefits mean…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… 49 sec, process no 10099, OS thread id 3771312 starting index read mysql tables in use 1, locked 1 LOCK WAIT 3… 24 sec, process no 10099, OS thread id 4078512 starting index read, thread declared inside InnoDB 500 mysql tables in use 1… bytes/read” shows average size of read requests. For random IO these should be 16K – page size, for full table scan or index scan read…
Post: What MySQL buffer cache hit rate should you target
June 29, 2006 By 5 Comments
… as – how busy were drives with concurrent requests, how random were these reads/writes, how good was OS or SAN cache hit… your system which can do about 100 random IOs/sec and you see Innodb_data_reads incremented by 10 per second (mysqladmin… to see how many reads and writes are done and where do they come from (log writes, data/index reads etc) but you…
Post: When Does InnoDB Update Table Statistics? (And When It Can Bite)
October 6, 2011 By 7 Comments
… the MySQL optimizer choose the appropriate index for a query. If a table’s statistics or index cardinality becomes outdated, you might… MySQL and InnoDB plugin’s manual: Metadata commands like SHOW INDEX, SHOW TABLE STATUS and SHOW [FULL] TABLES (or their corresponding…, this can affect your server especially if the random index dives had to be read from disk. Here is an example of SHOW…
Post: Do you always need index on WHERE column ?
August 28, 2007 By 17 Comments
… again. Today I’ve read opinion that if we have clause WHERE has_something=1 we should have index on column `has… first case has_something=0 for 90% of rows (with random distribution) mysql> select cnt0/cnt from (select count(*) cnt0 from….56 sec) Let’s check execution time with and without index mysql> select count(name) from testr force key (has_something…
