mysql - mysql分区修剪不适用于连接

Question

分区修剪不适用于连接。Posts 表由 tag_id 的 Range 分区。分区修剪没有加入并在 tag_id 字段上应用 where 条件。Mtom Table 只是用户选择的多对多表存储标签。

EXPLAIN PARTITIONS SELECT
posts.post_id,
posts.owner_id

FROM `posts_main` AS posts
JOIN `posts_tags_user_mtom` AS mtom
ON posts.tag_id = mtom.tag_id

ORDER BY posts.post_id DESC
LIMIT 50;

查询结果

帖子表分区

标签： mysqlmariadb

Answer 1

通过以下代码实现了这一点：

BEGIN
SELECT GROUP_CONCAT(`tag_id`) INTO @posts_tags FROM posts_tags_user_mtom WHERE `user_id` = 12 AND `active` = 1;

PREPARE stmt FROM CONCAT("EXPLAIN PARTITIONS SELECT posts.post_id,posts.owner_id FROM `posts_main` AS posts WHERE posts.tag_id IN(" ,@posts_tags, ") ORDER BY posts.post_id DESC;");

EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END