0

When I run a query on a large table on mysql, it takes a long time to run. But when I run the same query again, it takes a fraction of time than what it took the first time. Is MYSQL caching the resultset in the memory. How can I make it not do that? The RAM is filling up fast.

query_cache_size in my.ini is 0

The OS is windows server 2012 (if that matters).

Thanks

Ank
  • 6,040
  • 22
  • 67
  • 100

1 Answers1

0

It's probably the effect of InnoDB buffer cache.

And InnoDB buffer is explained here

The larger the buffer pool, the more InnoDB acts like an in-memory database, reading data from disk once and then accessing the data from memory during subsequent reads.

You may also want to have a look at the following threads:

mysql slow on first query, then fast for related queries

Mysql query run twice is must faster the second time even with SQL_NO_CACHE

Community
  • 1
  • 1
Fabian Bigler
  • 10,403
  • 6
  • 47
  • 70
  • This is probably whats happening. Which variable do I turn off for this to not happen. – Ank Jul 15 '14 at 17:11
  • I guess, that will be the innodb_buffer_pool_size. But IANAME (I Am Not A Mysql Expert) – Fabian Bigler Jul 15 '14 at 17:12
  • So innodb_buffer_pool_size allocates the RAM that Innodb will use for caching. After this number is used up, the database will start using the disk space. What I'm looking for is for server to remove the resultset from cache after it has run that. I'm not sure changing this number to a higher or lower value will solve that purpose. Please correct me if I'm wrong. – Ank Jul 15 '14 at 17:24
  • @Ank Not sure if setting the innodb_buffer_pool_size to 0 brings that effect. I'm afraid I can't doublecheck that since I have no MySQL DB running on my machine. Can you check that on a test server? – Fabian Bigler Jul 15 '14 at 18:04