Autmatically create table on MySQL server based on date?

Question

Is there an equivalent to cron for MySQL?

I have a PHP script that queries a table based on the month and year, like:

SELECT * FROM data_2010_1

What I have been doing until now is, every time the script executes it does a query for the table, and if it exists, does the work, if it doesn't it creates the table.

I was wondering if I can just set something up on the MySQL server itself that will create the table (based on a default table) at the stroke of midnight on the first of the month.

---

Update

Based on the comments I've gotten, I'm thinking this isn't the best way to achieve my goal. So here's two more questions:

1. If I have a table with thousands of rows added monthly, is this potentially a drag on resources? If so, what is the best way to partition this table, since the above is verboten?

2. What are the potential problems with my home-grown method I originally thought up?

Answer 1

Take a look at the Event Scheduler, available as of MySQL 5.1:

http://dev.mysql.com/doc/refman/5.1/en/events.html

Answer 2

Instead how about one table that has a month and year column or better yet a timestamp that you could query against.

Answer 3

If I have a table with thousands of rows added monthly, is this potentially a drag on resources?

---

It's the same number of rows if you pursued your monthly table split. Databases handle millions of rows - it's not an issue.

What are the potential problems with my home-grown method I originally thought up?

---

First would be the pain in the arse, joining 12 tables just to sum details over a year vs one table. More infrastructure and maintenance is needed to ensure that records in the correct table.

Answer 4

What was the goal of your original "scheduled table creation"? Were you worried about keeping all that data online? Maybe you wanted to remove the previous month's data for some sort of security reason?

If you really need to keep the months separate, your "scheduled table creation" idea has a synchronization problem. Scheduling an event for midnight does not guarantee it will be performed exactly at midnight. So what are you going to do if the scheduled creation just happens to occur a fraction of a second after an early-bird user blindly does an inquiry expecting a current table to exist? For that reason, keeping the empty table creation in the same place as the inquiry is a better idea.

OK, you might schedule the creation just before midnight. But if having an empty table online for a future date isn't a problem, why not just manually create the tables a year or two in advance?

Answer 5

If I have a table with thousands of rows added monthly, is this potentially a drag on resources? If so, what is the best way to partition this table, since the above is verboten?

Of course it uses resources because you adding data/information to the database. It is not a bigger drag on resources than having it different tables -- it should be less -- since every table has the overhead associated with maintaining the table.

Partition Issues

I don't think of it as "partitioning" but it totally depends on your data and how you use the data. In any SQL system you can pre-index based on columns or combination of columns. When you pre-index you are basically "partitioning". So if you know you are going to be looking at data most of the time based on dates (eg month/year) then you add an index on the date field. From an abstract perspective this is partitioning your data on date. What indexes you create depends on how you are going to use the data -- the more you create the more time/space is used to maintain the index(s) but the faster the performance for statements that use the index.

Answer 6

UPDATE pageviews SET 
        yesterday = CASE WHEN last_update_time > xxx_ts THEN yesterday ELSE today END,
        today     = CASE WHEN last_update_time > xxx_ts THEN today + 1 ELSE 1     END, 
        acc = acc + 1, last_update_time = UNIX_TIMESTAMP() WHERE page_id = 'xxx';

http://tech.shreeni.info/2010/01/conditional-mysql-statements-lifesaver.html

facebook.com/zahoor80