MYSQL moving information with php

I am wondering if it is possible to automate or by button press 开发者_Python百科to move mysql table information from one table to another table deleting it from the first table and putting it in another table? Using php.

My mysql table is big and the page that adds the information to that table has 70 query's on it which slows the page refresh times. I need to move information from the first table to the second at a certain time of day everyday so that those querys don't have to look through all of my giant 27k row table.

Is this possible?

Also if someone could help me with my comment on this page I would be grateful. link text

PHP doesn't have a constantly running server you can schedule background tasks with.

If you have access to the server you can set up a cron job (or scheduled task under windows) to run the PHP script for you.

Or (and this isnt so nice) you can put the script on the webserver and call it manually at the appropriate time by entering the URL in your browser.

A 27k row table is small by SQL standards, as long as it is properly indexed.

For instance, if you don't care about data from yesterday, you can add an indexed date column and filter with WHERE myDate > NOW() - INTERVAL 1 DAY, and SQL will automatically restrict the query to the rows younger than 24 hours.

I am wondering if it is possible to automate or by button press to move mysql table information from one table to another table deleting it from the first table and putting it in another table? Using php.

You can initiate it from PHP, but what you ask is effectively MySQL's domain.

It can be accomplished in two statements:

1. Use an INSERT INTO statement to copy the rows from the old table to the new one
2. Delete the old table

My preference would be that this occurs in a stored procedure for sake of a transaction and ease of execution (in case you want it initiated by CRON/etc) because it would be easier to call one thing vs a couple or more.

27k is not very big table and MySQL should work ok with that. Do you have all the required indexes? Did you used EXPLAIN on your slow queries? As for the question about moving data from one table to another - create a php script that will be run by CRON and will move rows one by one. What's the problem here?