Does MYSQL load the whole table into cache everytime?

Lets say I have a table, with say 1 million rows, with the first column being a primary key.

Then, if I run the following:

SELECT * FROM table WHERE id='tomato117' LIMIT 1

Does the table ALL get put into the cache (thereby causing the query to slow as more and more rows get added) or would the number of rows of the table not matter, sinc开发者_如何学Pythone the query uses the primary key?

edit: (added limit 1)

---

If the id is define as primary key, which only one record with value tomato117, so limit does not useful.

Using SELECT * will trigger mysql read from disk because unlikely all columns are stored into index. (mysql not able to fetch from index) In theory, it will affect performance.

However, your sql is matching query cache condition. So, mysql will stored the result into query cache for subsequent usage.

If you query cache size is huge, mysql will keep store all sql results into query cache until memory full.

This come with a cost, if there is an update on your table, query cache invalidation will be harder for mysql.

• http://www.mysqlperformanceblog.com/2007/03/23/beware-large-query_cache-sizes/
• http://www.mysqlperformanceblog.com/2006/06/09/why-mysql-could-be-slow-with-large-tables/

---

nothing of the sort.

It will only fetch the row you selected and perhaps a few other blocks. They will remain in cache until something pushes them out.

By cache, I refer to the innodb buffer pool, not query cache, which should probably be off anyway.

---

SELECT * FROM table WHERE id = 'tomato117' LIMIT 1

When tomato117 is found, it stops searching, if you don't set LIMIT 1 it will search until end of table. tomato117 can be second, and it will still search 1 000 000 rows for other tomato117.

http://forge.mysql.com/wiki/Top10SQLPerformanceTips

Showing rows 0 - 0 (1 total, Query took 0.0159 sec)
SELECT *
FROM 'forum_posts'
WHERE pid = 643154
LIMIT 0 , 30

Showing rows 0 - 0 (1 total, Query took 0.0003 sec)
SELECT *
FROM `forum_posts`
WHERE pid = 643154
LIMIT 1

Table is about 1GB, 600 000+ rows.

---

If you add the word EXPLAIN before the word SELECT, it will show you a table with a summary of how many rows it's reading instead of the normal results.

If your table has an index on the id column (including if it's set as primary key), the engine will be able to jump straight to the exact row (or rows, for a non-unique index) and only read the minimal amount of date. If there's no index, it will need to read the whole table.