I am looking a way to keep my mysql queries responsive. An approach would be to create a new database every week/month.

Is there a way to automatically dump the current database, name the database (by date), and create the new database, name the database (by date) with the required schema?

regards,

Why does creating a new database speed things up - do you keep loads of useless data in there? Sounds like a more fundamental issue than what you've described.

Apologies. There are actually two requirements:

-speed up queries
-archive & split the databases by time (either weekly/monthly). this is for data retention, SOX etc..

regards,

The date command gives you a date string which you can use in file naming. Like in: which produces a file name myfile_2008-04-21.

You can use the mysqldump command to make a dump from the database. See the man page for the numerous options mysqldump provides.

All other commands can be issued from the command line or a Bash script in the form:I recommend to put all commands in a Bash script, and call the Bash script as a cron job. Be sure to include full paths for everything you call or use in the file, cron jobs run in a different environment than your command line.

jlinkels

Partitioning may be an option. It basically divides tables according to certain rules.
You might need to change / add indexes to speed up your queries.