Re: make number of partitions variable
Hi Phil,
Thank you for your answer.
>Is there any reason _dateTime can't be stored as a datetime ?
Currently we use yyyymmddhhmmss as a date + time format. In some cases I’ve added a column to convert that format to a real datetime format when an insert occurs (using a trigger).
>I don't believe you can create a table such that it will auto create partitions for you if that's what you are trying to do.
>Note: _dateTime must be part of the primary key for partitions...
thanks for the note.
If _dateTime is a true datetime, then the optimizer will prune the partitions correctly:
explain partitions select * from herearetheticks where _dateTime < now();
+----+-------------+-----------------+---------------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+---------------------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | herearetheticks | p_2008-05-30,p_2008-05-31 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
+----+-------------+-----------------+---------------------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
[the stored procedure]
I’ve taken a look at your code for the stored procedure, but as far as I can tell, it will still be necessary to adjust the table manual, isn’t it? Or is it possible to do that with a stored procedure? (it is probably possible using PHP, or not?)
The only reason I would like to create partitions, is to keep the speed up. There are about 40.000 – 50.000 rows added per day.
When I just started filling the table, a specific query I need to use, took about 500 ms to run (on a dataset of about 50.000 rows. As the table gets bigger, the query gets slower. It now takes about 1.2 seconds (an that’s to much ;-)). I’ve checked for possibilities to speed it up with indexes (checked it with explain, whether or not they where used. The query took 6 seconds without the indexes I now use). So, that’s why I was thinking about partitioning.
Kind regards,
Matthijs