MySQL Forums
Forum List  »  Partitioning

Re: make number of partitions variable
Posted by: Mat private
Date: May 31, 2008 04:29AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: make number of partitions variable
2686
May 31, 2008 04:29AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.