Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

I have to store some sequence numbered data in MySQL. I have about 300,000 data items per day for about a 10 year span. Let's say the table structure is just sequence number (a big int) and data (a varchar). One very common query i'll be making is for all data for a single day, ordered by sequence number.

Storing the data in one table per day works really well. Querying all data from a table takes 0.8 seconds (which is acceptable), and adding order by sequence number takes it up to 1.0 seconds (still acceptable), but this does result in a massive number of tables that i'd rather have less of.

Storing in one table per month (and adding an indexed day number field), the time to get a day's data goes up to 1.6 seconds (not great), but adding partitioning by day number brings it right back down to 0.8.

However... when I add the order by sequence number to the partitioned table query, the time goes up to 2.5 seconds! Does this make sense? I would have thought that since my where clause (day number = X) instantly limits to all the data in a single partition, it should then be about the same performance as one of my original day tables (which it is, until I add the order by clause, then everything goes to hell).

It's like it is doing the order by before checking the partitions or something. Anyone got any ideas? (either "yes, that'll happen, and here's why", or "here's what you need to do to speed it up").

Thanks.

share|improve this question
An ORDER BY involves a sort. of course it's slower. If the working set for the sort doesn't fit in memory it'll have to go to disk, which makes it even slower. – Phil May 12 at 18:06
A few seconds for that volume of data is also OK. Not sure what you're expecting. People whinge on here about dealing with 1000 rows - you're dealing with plenty more than that – Phil May 12 at 18:08
I understand that the sort makes it slower, my question was why the sort only makes the query on the small table 20% slower, while the sort makes the query on the partition (containing exactly the same data as the small table and taking the same query time without ordering) 300% slower? – FieldEffect May 12 at 20:49
1  
Unless you post query plans for all of the scenarios, we can't really help you. Edit your question – Phil May 12 at 21:00

1 Answer

up vote 0 down vote

Assuming that you range partition your table by the (auto incrementing) sequence and you limit your query by another value (day number), you might see, that a lot of partitions will be touched when you use:

EXPLAIN PARTITIONS SELECT * FROM table_name WHERE day_number=X ORDER BY...

The reason for that is, that there is no linkage between the partitioning attribute and the data filter. Since MySQL uses a different index for each partition, it needs to open all partitions to find all data matching day_number.

If you know the partition name, maybe by querying INFORMATION_SCHEMA.PARTITIONS before the real select, and you use MySQL-5.6+, you can use partition selection like

SELECT * FROM table_name PARTITION(partition_name) WHERE day_number=X ORDER BY...

But this is just chitchat based on a lot of assumtions. To give you a real answer, you need to post the query plan and more information about table structure - like Phil said before.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.