MySQL Forums
Forum List  »  Partitioning

Re: Partitioning by hour
Posted by: Alejandro Boldt
Date: September 17, 2009 09:50AM

Partitioning has shown me its benefits, with things as ARCHIVE tables, in where indexes are not allowed. It does give a huge performance gain.

In my particular case, I create one table for each day, so hours between different days don't get mixed up. And in that particular SELECT, grouping doesn't do what I need.

So I was wondering if there's a way for MySQL to actually prune by hour when doing something like BETWEEN '05:00:00' AND '05:59:59', for example.

Rick James Wrote:
-------------------------------------------------------
> I've been monitoring this forum for a long time,
> and I have chatted with a developer, but I still
> come away the conclusion that Partitioning rarely
> delivers any performance boost.
>
> Even your use case (range scan over a single hour)
> would not necessarily be any faster with
> Partitions than without, assuming that you have an
> index on datetime.
>
> In your case, what happens tomorrow? Do you blow
> away the data and start with fresh partitions? If
> not, you are intermingling tomorrow's noon-hour
> data with today's. Is that what you want?
>
> What is the goal of the SELECT? Could it be
> accomplished by summarizing each hour's data into
> another, smaller, table; then query that summary
> table?

Options: ReplyQuote


Subject
Views
Written By
Posted
3521
September 11, 2009 01:32PM
2517
September 12, 2009 03:12PM
2328
September 12, 2009 09:31PM
3056
September 17, 2009 06:31AM
2424
September 17, 2009 08:45AM
Re: Partitioning by hour
2577
September 17, 2009 09:50AM
2404
September 21, 2009 03:55AM


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.