mysql - 我对像 Wall 这样的 facebook 的查询是否高效或者我配置错误

Question

我正在使用下面的查询创建一个类似 Facebook 的墙。

"SELECT * FROM (
            SELECT 1 AS `table`, 
            `comment_post_id` AS `feed_id`, 
            `blog_id` AS `from_blog`, 
            `comment_author` AS `author`, 
            `comment_content_stripped` AS `feed_title`, 
            `comment_content` AS `post_content_s`, 
            `type` AS `type`, 
            null AS `object_type`,
            `comment_date_gmt` AS `date` 
            FROM `wp_site_comments`
            UNION
            SELECT 2 AS `table`, 
            `post_id` AS `feed_id`, 
            null AS `from_blog`,  
            `blog_id` AS `author`, 
            `post_title` AS `feed_title`, 
            `post_content_stripped` AS `post_content_s`, 
            `post_type` AS `type`, 
            null AS `object_type`,
            `post_published_gmt` AS `date` 
            FROM `wp_site_posts`
        UNION
        SELECT 3 AS `table`, 
            `object_id` AS `feed_id`, 
            `blog_id` AS `from_blog`,  
            `user_id` AS `author`, 
            null AS `feed_title`, 
            null AS `post_content_s`, 
            `type` AS `type`, 
            `object_type` AS `object_type`,
            `date_added` AS `date` 
            FROM `wp_global_likes`
        UNION
        SELECT 4 AS `table`, 
            `object_id` AS `feed_id`, 
            null AS `from_blog`,  
            `user_id` AS `author`, 
            null AS `feed_title`, 
            null AS `post_content_s`, 
            `type` AS `type`, 
            `object_type` AS `object_type`,
            `date_added` AS `date` 
            FROM `wp_global_followers`
        ) AS tb
        ORDER BY `date` DESC"

结果返回正确，但我想知道查询是否有效，或者是否有更好的查询来完成此操作。

Answer 1

您可以使用 ORDER BY 而不需要子查询:

(SELECT …)
UNION
(SELECT …)
UNION
(SELECT …)
ORDER BY date DESC

引用自the manual :

To use an ORDER BY or LIMIT clause to sort or limit the entire UNION result, parenthesize the individual SELECT statements and place the ORDER BY or LIMIT after the last one.

由于您可能可以确定不会有任何重复行，因此您可以将隐式 UNION DISTINCT 更改为 UNION ALL 以避免不必要的删除重复。