MySQL Forums
Forum List  »  Partitioning

Are partition hints needed?
Posted by: Hubert Roksor
Date: May 21, 2007 11:32PM


I've searched in the forum and the manual and it seems this aspect hasn't been discussed yet. I think it could be useful to be able to give subtle hints (as in "non-binding") to the optimizer on the order the partitions should be read, or which partition a record is expected to be found.

Let's take a practical example, forum data. Let's say I partition my threads table by forum_id (RANGE, HASH, etc... I don't think it matters in that case) since most of the queries involve the forum_id one way or another. The most common query would be listing the latest threads of a forum, eg
SELECT * FROM threads WHERE forum_id = 1
In this case, all but one partition will be pruned and only the correct one will be scanned. Now, what if we want to retrieve a thread by its "thread_id"? (the PRIMARY key of the table) Will MySQL have to scan each partition until it finds the right one? or does the PRIMARY index already contain a reference to the partition the record is stored in?

If it doesn't, that's where I'd like to be able to give a hint to the optimizer. Most of the time, the application already knows in which forum a thread is (the forum_id could be part of the link, for example), so we could add the condition to the WHERE clause as in
SELECT * FROM threads WHERE thread_id = 123 AND forum_id = 1
and force the optimizer to prune away the other partitions, but what if the thread moved between the time the link was generated and the query executed? The record may have been moved to another partition and that query would fail and then the application would be forced to issue a second query omitting the "forum_id = 1" part to determine whether the thread was moved or deleted. Now, instead of adding an extra WHERE condition we could give a hint to the optimizer to signal that we think the record is in the partition that corresponds to "forum_id = 1" but that wouldn't prevent it to scan the other partitions if necessary. It could take that form, similar to the USE INDEX hint, except that we feed it a predicate instead of a name:
FROM threads /*!50119 USE PARTITION (forum_id = 1) */
WHERE thread_id = 123

Thanks for reading :)

Edited 1 time(s). Last edit at 05/21/2007 11:33PM by Hubert Roksor.

Options: ReplyQuote

Written By
Are partition hints needed?
May 21, 2007 11:32PM

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.