这个SQL语句有更有效的版本吗？
我得到了下面的SQL语句，它在MySQL中运行在WordPress安装上。我正在寻找符合特定标准的元数据，并在某个日期之后创建。
但是，WordPress不存储元数据被添加到帖子中的日期，所以我创建了元数据本身(wpcf-post-like)、它添加的日期(wpcf-post-like- date )和第三块元数据，该元数据是同时创建的，其中包含了前两个由感叹号分隔的ID (wpcf-post-like- date -link)。
然后，我使用下面的SQL语句在特定日期后在由当前作者编写的帖子(例如ID包含在下面的SQL代码中)上获得赞。它工作，但它需要7-8秒左右的运行，这是远远不理想的。是否有一个更有效的版本相同的声明？
SELECT `meta_id` FROM `wp_postmeta`
WHERE `post_id` IN (
SELECT `ID` FROM `wp_posts` WHERE post_author = $user_id
) AND
`meta_value` IN (
SELECT CONCAT(combined_ids_a.`meta_id`, '!', combined_ids_b.`meta_id`) AS `combined_meta_id` FROM (
SELECT `meta_id`, 'like_id' AS `meta_type` FROM `wp_postmeta`
WHERE `meta_key` LIKE 'wpcf-post-likes'
AND `meta_value` NOT LIKE '1837'
AND `meta_id` IN
(SELECT SUBSTRING_INDEX(`meta_value`, '!', 1) FROM `wp_postmeta` WHERE `meta_key` LIKE 'wpcf-post-like-date-link' ORDER BY `meta_id` DESC)
UNION
SELECT `meta_id`, 'like_date' AS `meta_type` FROM `wp_postmeta`
WHERE `meta_key` LIKE 'wpcf-post-like-date'
AND `meta_value` > '01-02-2016 09:20:34'
AND `meta_id` IN
(SELECT SUBSTRING_INDEX(`meta_value`, '!', -1) FROM `wp_postmeta` WHERE `meta_key` LIKE 'wpcf-post-like-date-link' ORDER BY `meta_id` DESC)
) AS combined_ids_a JOIN
(
SELECT `meta_id`, 'like_id' AS `meta_type` FROM `wp_postmeta`
WHERE `meta_key` LIKE 'wpcf-post-likes'
AND `meta_value` NOT LIKE '1837'
AND `meta_id` IN
(SELECT SUBSTRING_INDEX(`meta_value`, '!', 1) FROM `wp_postmeta` WHERE `meta_key` LIKE 'wpcf-post-like-date-link' ORDER BY `meta_id` DESC)
UNION
SELECT `meta_id`, 'like_date' AS `meta_type` FROM `wp_postmeta`
WHERE `meta_key` LIKE 'wpcf-post-like-date'
AND `meta_value` > '01-02-2016 09:20:34'
AND `meta_id` IN
(SELECT SUBSTRING_INDEX(`meta_value`, '!', -1) FROM `wp_postmeta` WHERE `meta_key` LIKE 'wpcf-post-like-date-link' ORDER BY `meta_id` DESC)
) AS combined_ids_b
);回答 1
Stack Overflow用户
发布于 2016-02-01 19:14:05
嗯，你在处理一个键值表，所以性能问题是很自然的。
交叉自加入一个临时视图(派生表)，这是非常特殊的事情，但对DBMS来说不应该是一个大问题。它应该注意到，您的查询中有两次相同的子查询，并且只处理了一次。(但是，当交叉连接时，您应该显式地使用CROSS JOIN，以便表明这是有意的，而且您并不像使用简单的JOIN建议的那样仅仅忘记了ON子句。)
那么还有什么需要优化的呢？我已经告诉过您，如果可能的话，应该将UNION替换为UNION ALL，并删除IN子句中的ORDER BY。此外，您使用LIKE和NOT LIKE，尽管您不寻找模式。也许您是幸运的，DBMS预先扫描您的字符串文本，并注意到根本没有可搜索的模式，但也许没有。不要给DBMS做太多的工作，而使用=和<>。
然后创建一个从未使用过的meta_type列，那么为什么要创建它呢？
Anayway，您应该有两个用于此查询的复合索引:一个在wp_postmeta(meta_key、meta_id、meta_value)上，以便快速找到元ID。另一个在wp_postmeta上(meta_key，meta_value)。顺便问一下:您不能为最外层的查询指定meta_key吗？在没有键的情况下查找值看起来很奇怪。
查询内容如下：
select meta_id
from wp_postmeta
where post_id in (select id from wp_posts where post_author = $user_id)
and meta_value in
(
select concat(a.meta_id, '!', b.meta_id)
from
(
select meta_id
from wp_postmeta
where meta_key = 'wpcf-post-likes'
and meta_value <> '1837'
and meta_id in (select substring_index(meta_value, '!', 1) from wp_postmeta where meta_key like 'wpcf-post-like-date-link')
union all
select meta_id
from wp_postmeta
where meta_key = 'wpcf-post-like-date'
and meta_value > '01-02-2016 09:20:34'
and meta_id in (select substring_index(meta_value, '!', -1) from wp_postmeta where meta_key like 'wpcf-post-like-date-link')
) a
cross join
(
select meta_id
from wp_postmeta
where meta_key = 'wpcf-post-likes'
and meta_value <> '1837'
and meta_id in (select substring_index(meta_value, '!', 1) from wp_postmeta where meta_key like 'wpcf-post-like-date-link')
union all
select meta_id
from wp_postmeta
where meta_key = 'wpcf-post-like-date'
and meta_value > '01-02-2016 09:20:34'
and meta_id in (select substring_index(meta_value, '!', -1) from wp_postmeta where meta_key like 'wpcf-post-like-date-link')
) b
);或者用OR代替UNION ALL
select meta_id
from wp_postmeta
where post_id in (select id from wp_posts where post_author = $user_id)
and meta_value in
(
select concat(a.meta_id, '!', b.meta_id)
from
(
select meta_id
from wp_postmeta
where
(
meta_key = 'wpcf-post-likes'
and meta_value <> '1837'
and meta_id in (select substring_index(meta_value, '!', 1) from wp_postmeta where meta_key like 'wpcf-post-like-date-link' order by meta_id desc)
)
or
(
meta_key = 'wpcf-post-like-date'
and meta_value > '01-02-2016 09:20:34'
and meta_id in (select substring_index(meta_value, '!', -1) from wp_postmeta where meta_key like 'wpcf-post-like-date-link' order by meta_id desc)
)
) a
cross join
(
select meta_id
from wp_postmeta
where
(
meta_key = 'wpcf-post-likes'
and meta_value <> '1837'
and meta_id in (select substring_index(meta_value, '!', 1) from wp_postmeta where meta_key like 'wpcf-post-like-date-link' order by meta_id desc)
)
or
(
meta_key = 'wpcf-post-like-date'
and meta_value > '01-02-2016 09:20:34'
and meta_id in (select substring_index(meta_value, '!', -1) from wp_postmeta where meta_key like 'wpcf-post-like-date-link' order by meta_id desc)
)
) b
);还有一件事: meta_value包含一个字符串。如果键是‘wpcf-post date’，您希望它包含一个日期时间字符串。然而，它仍然是一个字符串，所以'02-01-1980 09:20:34‘比'01-02-2016 09:20:34’要大。为了能够比较日期时间，您应该存储‘yyyy:mi:ss’，或者转换为日期时间或时间戳。
https://stackoverflow.com/questions/35131109
复制相似问题
腾讯云开发者
