May I benefit from MySQL partitions?

Question

I have a database stored on a single SSD. I'm thinking that this enough tells me that I don't need partitioning, but still, I want to fully verify this.

My big table (let's call it times) has a column that represents a time of the day, stored as a dumb integer. For example '12:07:31' is stored as 120731.

There is a foreign key from this table to another one (which we'll call trips); and this is linked to a last one (called calendar) which contains a date information.

Currently I have a script that runs every night and:

• filters the calendar table to retrieve only the rows of today
• from this, select all the information from the times that match only today
• from this, create a table for every hour+3

By 'every hour+3', I mean I have these tables:

• times_00 => from 12am to 3am
• times_01 => from 1am to 4am
• times_02 => from 2am to 5am
• ...
• times_19 => from 7pm to 10pm
• ...

So when I want to query times, I just query times_10 between 10am and 11am; or times_17 between 5pm and 6pm, etc.
This is MUCH faster because I go from 10M to <200k rows.

Now I have a feeling that I could benefit from partitioning, but how to partition:

• over the date: it's not a column of the table, but rather a column in a foreign table. Is it possible to use JOINs in partition rules?
• over the time: I know I could partition using RANGE, but this won't get me overlapping partitions: 0-3, 1-4, 2-5...

Is it possible? Is it worth it?

---

You will find here all the data needed to reproduce my setup.

Here's the output of ./test.sh:

stop_times:

real    0m0.487s
user    0m0.004s
sys     0m0.000s
stop_times_part:

real    0m0.707s
user    0m0.000s
sys     0m0.004s
stop_times_14:

real    0m0.127s
user    0m0.004s
sys     0m0.000s

The results are variable, but I observed a similar time for the first two methods (around 500ms), and a 4x faster time for the last method (around 125ms)

Answer 1

As I understand, You want to partition the "time" table row into different table. So that your query become fast. But I don't understand why u want to create 'every hour+3' table instead of 'every hour' table. If you generate every hour table then i think you have no problem of overlapping and every row of time table will be the foreign key of 'every hour' table.

Eg.

every_hour_12_03_2015_01 => for 12AM to 1 AM
every_hour_12_03_2015_02 => for 1AM to  2 AM
every_hour_12_03_2015_03 => for 2AM to  3 AM

:
:

every_hour_12_03_2015_24 => for 11PM to 12 AM