Trick to place a table into memory with SELECT COUNT(*) ?

MySQL & MariaDB Other MySQL® Questions
1

Hello,

I have a faint memory of reading somewhere that by doing SELECT COUNT(*) FROM A, table A will be loaded into memory.

Is this true? If so, is it true for InnoDB table? And where exactly is it placed - InnoDB Buffer Pool (as long as there is a sufficient space there)?

Thank you!

2

It’s not universally true. But for InnoDB tables, it’ll choose some index to scan (possibly the primary key, possibly something else) and provided that index fits into the buffer pool fully, afterwards all the pages from the index will be in the buffer pool.

Peter wrote a blog post about this: http://www.mysqlperformanceblog.com/2008/05/01/quickly-prelo ading-innodb-tables-in-the-buffer-pool/

3

Hello,

Thank you for the link. I believe that is exactly where I read it before.

Another question I have is: Is it known how long such index/table will stay in memory? Will it be just decided by the part of the mysql managing InnoDB Buffer Pool, and is out of our control?

Is there a way to look into InnoDB Buffer Pool to see what is there?

I ask because we have a stored procedure which uses 2 tables. When I run it, it takes up to 1.5 seconds (depending on the input). However, if I SELECT COUNT() FROM A and SELECT COUNT() FROM B and then run it, it takes at most 0.25 seconds. I decided to schedule a cron job to run these selects once an hour. Is that too often? If my database is 20 GB and InnoDB Buffer Pool Size is 30 GB, can I assume that these tables will always stay in memory?

Thanks a lot!

4

InnoDB has a least recently used policy for deciding what to delete from the buffer pool.

The most recent version of innodb plug-in no longer puts an entire table into the buffer pool on a full table scan.

5

gmouse - thank you for the info.

A few more questions:

  1. What is the most recent innodb plug-in? Is it part of the latest mysql release? We are using version 5.0.44, so it should not affect us, right?

  2. I thought that entire table does not go into the buffer pool anyway. What I understood from reading Peter’s article is that we can either put a clustered index or a non-clustered index(es) into the buffer pool. Did I misunderstand something?

Thank you!

6
  1. Look at the innodb website for the most recent version, but it will only work in MySQL 5.1
  2. Some rows do get into the buffer pool. The clustered index is the table itself, so it will not entirely fit into memory either.
7

gmouse,

I’m still a bit confused. In regards to point 2, if the table is 1 GB in size, and we have more than 1 GB in the InnoDB Buffer Pool, and we do the following:

SELECT COUNT(*)
FROM A
WHERE non_indexed_column = 0

thereby loading the clustered index and thus the table data itself into the memory, why won’t the entire table load?

Thank you!

8

maybe you must select all fields, I don’t know

it should fully load in mem

9

gmouse,

I guess there was some misunderstanding on my part )

In previous note you said: “The clustered index is the table itself, so it will NOT entirely fit into memory either”

So I was wondering why it will NOT.

Now it seems you are saying “it SHOULD fully load”.

So, I’ll take the last statement as true then.

Thank you!

10
  1. I thought that entire table does not go into the buffer pool anyway.

that made me suspect that you didn’t have enough memory; if you have sufficient memory, the entire table will fit.

11

gmouse,

We recently increased memory on our machines to 35 GB and set the InnoDB Buffer Pool Size to 30 GB. Our current DB size is 20 GB and the largest table is ~ 4GB, so everything seems to fit nicely.

We did get a performance boost.

Thank you!