Does MYSQL always load the entire table into the cache?

Question

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

Then, if I run the following:

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

Is the ALL table written to the cache (thereby causing the query to slow down as more rows are added) or will the number of rows in the table not matter because the query uses the primary key?

edit: (added restriction 1)

Answer 1

nothing like this.

It will select only the selected row and possibly several other blocks. They will remain in the cache until something pushes them out.

The cache refers to the innodb buffer pool, not the request cache, which should probably be disabled.

Answer 2

id , tomato117, .

SELECT * mysql , . (mysql ) .

sql . , mysql .

, mysql sql .

, , 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/

Answer 3

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

tomato117, , LIMIT 1, . 117 , - 1 000 000 117.

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

1 , 600 000+ .

Answer 4

EXPLAIN SELECT, , , .

If your table has an index in the column id(including if it is specified as a primary key), then the engine will be able to go directly to a specific row (or row for an imperfect index) and just read the minimum date size. If there is no index, he will need to read the entire table.