Re: Partition selection on join queries

[Rick James]

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.)