Re: Are partition hints needed?
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 :)