my site has lots of incoming searches which is stored in a database to show recent queries into my website. due to high search queries my database is getting bigger in size. so what I want is I need to keep only recent queries in database say 10 records. this keeps my database small and queries will be faster.
I am able to store incoming queries to database but don't know how to restrict or delete excess/old data from table.
any help??
well I am using PHP and MySQL
You should write a PHP script, which will be started by CRON (ie. once a day) and move some data from main table TableName to archive table TableNameArchive with exactly the same structure.
That SQL inside the script should looks like:
INSERT INTO TableNameArchive
SELECT * FROM TableName WHERE data < '2010-06-01' //of course you should provide here your conditions
next you should DELETE old records from TableName.
Hopefully you have a timestamp column in your table (or have the freedom to add one). AFAIK, you have to add the timestamp explicitly when you add data to the table. Then you can do something along the lines of:
DELETE FROM tablename WHERE timestamp < '<a date two days in the past, or whatever'>;
You'd probably want to just do this periodically, rather than every time you add to the table.
I suppose you could also just limit the size to the most recent ten records by checking the size of the table every time you are about to add a line, and deleting the oldest record (again, using the timestamp column you added) if adding the new record will make it too large.
Falkon's answer is good - though you might not want to have your archive in a table, depending on your needs for that forensic data. You could also set up a cron job that just uses mysqldump to make a backup of the database (with the date in the filename), and then delete the excess records. This way you can easily make backups of your old data, or search it with whatever tool, and your database stays small.