MySQL Forums
Forum List  »  General

Re: Linking to a LUT more than once
Posted by: Rick James
Date: August 10, 2011 07:07PM

> I guess when I hear 'scan' I think of disk I/O. Should I not be? Is that not what you mean?

(Sorry if I am repeating stuff you already know...)

No. "table scan" and "index scan" refer to going through all(?) of the rows in the data or an index. If they are all cached, there is no I/O.
* MyISAM caches the index blocks in the key_buffer, but lets the OS cache the data blocks.
* InnoDB caches both data and index blocks in its buffer_pool.
* MEMORY is always entirely in RAM; no caching needed.

When mysqld is first started, the caches are cold, and a table scan will hit the disk a lot. Later, the whole table might be cached and there would be no I/O.

When scanning a table (or index) that is too big to be cached, the scan will effectively 'blow out cache', that is, bump everything out of cache. The scan will take time and do at least some I/O. Subsequent queries will probably find nothing they need in cache and do more I/O.

"Range scan" (if it is even a proper term) is similar to table scan, but it is scanning just part of the table or index. Example: WHERE x BETWEEN ... AND ... (where x is indexed)

The BTrees that MySQL uses are optimized for scans -- when it is finished with one block, it has a link to the next block. This is a minor optimization over having to go up the BTree, then back down.

None of what I have said, above, refers to the "Query" cache. The QC is a totally separate key-value cache in RAM that remembers SELECT statements and their resultsets. It has lots of issues, especially for tables that have frequent writes to them. If the QC is enabled, then running exactly the same query twice in a row -- the second one will (usually) take virtually zero time.

The QC has a tunable limit on how big the thing being saved is. That is, a SELECT that returns a lot of rows, or wide rows (eg, big TEXT field), will not be put into the QC. Items in the QC are bumped out in an LRU manner (I think). ANY change to a table will purge ALL entries in the QC for that table. The algorithm for that purging is not very efficient, so a large QC leads to the system "stalling" mysteriously.

Shawn, can I elaborate on anything else for you?

Options: ReplyQuote


Subject
Written By
Posted
Re: Linking to a LUT more than once
August 10, 2011 07:07PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.