MySQL performance with partition option.

Question

Hello,

I've designed mysql tables for bookings. One of them has hotels_id, rt_id, -> (room_type_id), date .....

Every rt_id has 365 rows (date). And every hotels_id has some rt_id (room types)

for example 500 hotels and 8 room types per hotel -> 500 x 8 x 365 = 1.460.000 rows.

I've used column index option for " hotels_id, rt_id, date " . But I also need to get more performance for getting datas.

I think to create the cron job or Event Scheduler Configuration to delete old partitions and to create new partitions (for every month).

So I will fix that as 1 year : 1.460.000 rows.

Here I think to use MySQL partiton option for months. 12 partitions for 12 months and cron job or Event Scheduler Configuration for new and old months.

My question is what do you think about my idea. Is that good for more performance?

Or any other suggestions?

Thanks.

Answer 1

I am doing something similar too but I am steering clear of cron jobs. Event scheduler should work. Once I have some code done, I will post a sample :)

ETA: ok here is another question that already has some good information http://stackoverflow.com/questions/1786579/can-mysql-create-new-partitions-from-the-event-scheduler