通过存储在“外部”表中的值来改进SQL查询以过滤结果

To make things easier, let's say that I have a table representing pretty simple user's feed.

为了方便起见,我们假设我有一个代表非常简单的用户提要的表。

There are two "key" columns in my feed table:

我的Feed表中有两个“key”列:

object_id it's a ID of different assets, e.g. comment, post, etc.

object_id它是不同资产的ID,例如评论,发布等

entity_type_id it's a basically reference to another table in my DB.

entity_type_id它基本上是对我数据库中另一个表的引用。

The "children" tables may have some attributes in common, e.g. is_hidden, is_deleted and is_locked (however, they are not replicated across all tables).

“子”表可以具有一些共同的属性,例如is_hidden,is_deleted和is_locked(但是,它们不会在所有表中复制)。

Now, I'd like to implement a filter that should filter out my feed items, based on the values of these three attributes.

现在,我想实现一个过滤器,根据这三个属性的值,过滤掉我的Feed项。

What I did so far?

到目前为止我做了什么?

SELECT `f`.* 
FROM `feed` `f`
WHERE 1
-- !!! Other filters goes here. ---
AND 
(
    --
    -- !!! Filter by status
    --
    (   -- "Locked" (not all children tables have this column)
        (
            `f`.`entity_type_id` = 1 AND `f`.`object_id` IN ( SELECT `fb_comment_id` FROM `comments` WHERE `is_locked` = 1  AND `fb_page_id` IN('0123456789') )
        )
        OR
        (
            `f`.`entity_type_id` = 4 AND `f`.`object_id` IN ( SELECT `fb_post_id` FROM `posts` WHERE `is_locked` = 1 AND `fb_page_id` IN('0123456789') )
        )
    ) 
    (   -- "Hidden" (not all children tables have this column)
        (
            `f`.`entity_type_id` = 1 AND `f`.`object_id` IN ( SELECT `fb_comment_id` FROM `comments` WHERE `is_hidden` = 1  AND `fb_page_id` IN('0123456789') )
        )
        OR
        (
            `f`.`entity_type_id` = 4 AND `f`.`object_id` IN ( SELECT `fb_post_id` FROM `posts` WHERE `is_hidden` = 1 AND `fb_page_id` IN('0123456789') )
        )
    ) 
    OR 
    (
        -- "Deleted"
        (
            `f`.`entity_type_id` = 1 AND `f`.`object_id` IN ( SELECT `fb_comment_id` FROM `comments` WHERE `is_deleted` = 1 AND `fb_page_id` IN ('0123456789') )
        ) 
        OR
        (
            `f`.`entity_type_id` = 3 AND `f`.`object_id` IN ( SELECT `insta_comment_id` FROM `instagram_comments` WHERE `is_deleted` = 1 AND `insta_profile_id` IN ('9876543210') )
        )
        OR 
        (
            `f`.`entity_type_id` = 4 AND `f`.`object_id` IN ( SELECT `fb_post_id` FROM `posts` WHERE `is_deleted` = 1 AND `fb_page_id` IN ('0123456789') )
        )
        OR 
        (
            `f`.`entity_type_id` = 5 AND `f`.`object_id` IN ( SELECT `insta_post_id` FROM `instagram_posts` WHERE `is_deleted` = 1 AND `insta_profile_id` IN ('9876543210') )
        )
    )
)

As you can see I was using sub queries, but I was wondering is there a better way to write such queries?

正如您所看到的,我正在使用子查询,但我想知道是否有更好的方法来编写此类查询?

2 个解决方案

#1

I don't know if it's better, but I'd create a subquery that unions the necessary flag fields from your child tables and then just do a regular join to get the flag fields. If a flag field is not present for one of the tables, it can just be false.

我不知道它是否更好,但我创建了一个子查询,它可以从子表中联合必要的标志字段,然后只需要定期连接来获取标志字段。如果其中一个表不存在标志字段,则它可能只是假。

Something like:

SELECT `f`.* 
FROM `feed` `f`
JOIN
    (
        SELECT  
            1 AS `entity_type_id`
        ,   fb_comment_id AS `object_id`
        ,   is_locked
        ,   is_hidden
        ,   is_deleted
        FROM
            comments

        UNION ALL

        SELECT  
            4 AS `entity_type_id`
        ,   fb_post_id AS `object_id`
        ,   is_locked
        ,   is_hidden
        ,   is_deleted
        FROM
            posts

        UNION ALL

        SELECT  
            3 AS `entity_type_id`
        ,   insta_comment_id AS `object_id`
        ,   0 AS is_locked
        ,   0 AS is_hidden
        ,   is_deleted
        FROM
            instagram_comments

        UNION ALL

        SELECT  
            5 AS `entity_type_id`
        ,   insta_post_id AS `object_id`
        ,   0 AS is_locked
        ,   0 AS is_hidden
        ,   is_deleted
        FROM
            instagram_posts
    ) AS flag_summary ON (
        flag_summary.entity_type_id = f.entity_type_id
    AND flag_summary.object_id = f.object_id
    )

#2

Some tips:

Try to use INNER JOIN instead of WHERE + correlated queries. Create for example a table with all the tables in the sub-queries, and apply your filters. Do not forget to use PROCEDURE ANALYSE and index.

尝试使用INNER JOIN而不是WHERE +相关查询。例如,创建一个包含子查询中所有表的表,并应用您的过滤器。不要忘记使用PROCEDURE ANALYZE和索引。

Avoid SELECT *, type all the variables you need.

避免使用SELECT *,键入所需的所有变量。

Apply an EXPLAIN to know where you can improve your script.

应用EXPLAIN以了解可以改进脚本的位置。

#1