MySQL Forums
Forum List  »  Partitioning

Re: Are partition hints needed?
Posted by: Mikael Ronström
Date: May 24, 2007 12:22PM

Hi,

Hubert Roksor Wrote:
-------------------------------------------------------
> Hi,
>
> 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.
>

This topic was discussed internally in MySQL and we decided
to not support it but rather let the optimizer handle it.
If the feature existed it would not be just a hint, it would
be an order, so the query example you provided would be giving
the same result as the query with both conditions.

Rgrds Mikael


> 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 = 1In 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 = 1and 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:
> SELECT *
> FROM threads /*!50119 USE PARTITION (forum_id = 1)
> */
> WHERE thread_id = 123
>
> Thanks for reading :)

Options: ReplyQuote


Subject
Views
Written By
Posted
4061
May 21, 2007 11:32PM
Re: Are partition hints needed?
2395
May 24, 2007 12:22PM


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.