The key to your question is InnoDB. InnoDB keeps data and indexes in its buffer using LRU to flush. So the 2nd time your data was already in memory. Depending on your buffer size and database size you have all of the advantages of a memory resident database for frequently used data without the disadvantages of losing the data on system shutdown.
-----Original Message----- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Thursday, March 10, 2005 1:43 PM To: Bob O'Neill Cc: mysql@lists.mysql.com Subject: Re: Slow queries only the first time On Mar 10, 2005, at 11:20 AM, Bob O'Neill wrote: > Hello. I am wondering why some of my queries are slow on the first > run, but > speedy on subsequent runs. They are not being query cached, as I have > query_cache_type set to DEMAND. Is it something as simple as pulling > the > data into RAM from disk, or is there something else going on? Here's a > simple example: > > mysql> select count(*) from foo; > +----------+ > | count(*) | > +----------+ > | 1374817 | > +----------+ > 1 row in set (3.60 sec) A table scan. > mysql> select count(*) from foo; > +----------+ > | count(*) | > +----------+ > | 1374817 | > +----------+ > 1 row in set (0.92 sec) Another table scan, but now some or all of the table is cached in RAM (disk cache) by the OS (not by MySQL). > mysql> show variables like 'query_cache_type'; > +------------------+--------+ > | Variable_name | Value | > +------------------+--------+ > | query_cache_type | DEMAND | > +------------------+--------+ > 1 row in set (0.00 sec) You could probably verify that the query cache wasn't used by monitoring the query cache stats <http://dev.mysql.com/doc/mysql/en/query-cache-status-and- maintenance.html>. > I am running MySQL 4.1.10 with InnoDB on RHEL 2.1 (kernel > 2.4.9-e.49enterprise). Binaries are Linux x86 glibc static gcc RPMs > from > mysql.com. > > Thanks, > -Bob Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
