MySQL Forums
Forum List  »  Partitioning

Re: Partition selection on join queries
Posted by: Rick James
Date: March 19, 2011 12:23PM

Here's an ugly workaround:

mysql> SELECT  @a := MIN(num),
    ->         @z := MAX(num)
    ->     FROM tbl_co
    ->     WHERE reg = 8;
+----------------+----------------+
| @a := MIN(num) | @z := MAX(num) |
+----------------+----------------+
|             15 |             16 |
+----------------+----------------+

mysql> explain partitions
    ->     select *
    ->         from tbl_test  t
    ->         inner join tbl_co c on t.num = c.num
    ->         where c.reg = 8
    ->         AND t.num BETWEEN @a AND @z;    -- I added this
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | Extra                          |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+--------------------------------+
|  1 | SIMPLE      | c     | NULL       | ref  | reg           | reg  | 5       | const |    2 | Using where                    |
|  1 | SIMPLE      | t     | p3         | ALL  | NULL          | NULL | NULL    | NULL  |   17 | Using where; Using join buffer |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+--------------------------------+

(I hope, in real life, you have an index on `reg`. I added such, but that was not sufficient.)

Options: ReplyQuote


Subject
Views
Written By
Posted
3105
March 14, 2011 02:48AM
Re: Partition selection on join queries
1520
March 19, 2011 12:23PM


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.