How do you usually warm up your database ?
I run a similar queries to this for every table in DB:
SELECT * FROM ip_log ORDER BY ID;
SELECT ip, member_id FROM ip_log ORDER BY ip, member_id;
Is that the best way, or there is something cooler ?
MySQL
How do you usually warm up your database ?
I run a similar queries to this for every table in DB:
SELECT * FROM ip_log ORDER BY ID;
SELECT ip, member_id FROM ip_log ORDER BY ip, member_id;
Is that the best way, or there is something cooler ?
Best Answer
If you would like to warm your MyISAM Key Buffer you could run this:
Here is the output:
This displays every possible query you could run against MyISAM tables selecting index column fields only. Running those queries will populate the MyISAM Key Buffer.
Here is a similar query to make queries that preload the InnoDB Buffer Pool
Here is a similar query to make queries that preload the InnoDB Buffer Pool and the MyISAM Key Buffer (I posted this back in November 2011)
If you see the same query appear more than once in a display, this indicates the presence of redundant indexes.
Give it a Try !!!
UPDATE 2012-02-04 21:53 EDT
While one can create caches dedicated to MyISAM via as follows:
you must still decide to load all indexes or specific ones. The script I provided simply shows you the list of all possible indexes you can load. You are not obligated to load all indexes, just the ones you know are used the most. If you have the hardware and budget, you load as much as you like into the dedicated MyISAM caches.
Here is something else to consider: All the queries mentioned are actually accessing covering indexes. Running those queries will only access the MyISAM pages of the .MYI file and only the index pages of an InnoDB tables. Table data is never retrieved. In light of this, all or selected queries populated index pages regardless of storage engine.
More Links on the Principles of Covering Indexes
UPDATE 2012-02-04 22:05 EDT
Quick note about InnoDB vs MyISAM. You can cache data pages for InnoDB. MyISAM no.