[Solved] Growing data to another table - DB Designing
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

Growing data to another table - DB Designing

Database type:

My current project is a social media app somewhat like Facebook. Now the post created by both users and news post (there is a cron running every 15 min and it fetch latest news from various news channels) are keeping in the same table called post table. Because of news post the table is growing very fast and the timeline taking more time to load. So we are planing to slit normal post (post table) and news post (news_post table) to separate tables and then slit old news post to a backup a table (news_post_backup table).
Then on listing post API we have to take union of all these 3 tables and have to sort by post create time and have to take post based on pagination data and other conditions
I want to know is there any benefit from doing like this. I am doubtful because I have to take union then its again become same table like the previous table structure

MYSQL Version on server is 5.6

UPDATE Here I am adding more information
The Query I am running is 

select CP.id,CP.user_id,post_title,post_content,post_type,new_title,is_spam,spam_reportedby,CP.privacy,CP.link_title,CP.link_content,CP.link_image,CP.is_paid,CP.payment_status,CP.is_breaking,
CUP.id as channel_userspost_id,CUP.parent_id,
SU.full_name as reporteduser_full_name,SU.user_name as reporteduser_user_name,
SU.user_profile_pic as reporteduser_user_profile_pic,
FU.id as from_user_id, FU.full_name as from_user_full_name,
FU.user_name as from_user_name,
FU.user_profile_pic as from_user_profile_pic,
TU.id as to_user_id, TU.full_name as to_user_full_name,
TU.user_name as to_user_name,
TU.user_profile_pic as to_user_profile_pic,
TUA.authentication_status as to_user_authentication_status,
FUA.authentication_status as from_user_authentication_status,
C.verification_status as channel_verification_status,
CUP.created_at,CUP.updated_at,
guid,external_url,
CP.channel_id,CP.rss_channel_id,if(CP.rss_channel_id!=0,RC.rss_name,C.channel_name) as channel_name,
if(CP.rss_channel_id!=0,RC.rss_logo,C.profile_pic) as channel_logo,
C.channel_type,
PCD.like_count as like_count,
PCD.search_count as search_count,
PCD.view_count as view_count,
CM.channel_member_status,C.payment_status as channel_payment_status,C.payment_method as channel_payment_method,
CP.is_live_finished from `channel_users_posts` as `CUP` inner join `channel_posts` as `CP` on `CUP`.`channel_post_id` = `CP`.`id` and `is_spam` = 'N' 
left join `channels` as `C` on `CP`.`channel_id` = `C`.`id` 
left join `rss_channels` as `RC` on `CP`.`rss_channel_id` = `RC`.`id` left join `channel_members` as `CM` on `CM`.`channel_id` = `C`.`id` and `CM`.`user_id` = 427 and `CM`.`channel_member_status` != -1 
left join `test_develop_new`.`users` as `FU` on `FU`.`id` = `CUP`.`shared_from` left join `test_develop_new`.`users` as `SU` on `SU`.`id` = `CP`.`spam_reportedby` 
left join `test_develop_new`.`users` as `TU` on `TU`.`id` = `CUP`.`user_id` left join `common_auth_develop_new`.`user_authentication` as `FUA` on `FUA`.`user_id` = `FU`.`id` 
left join `common_auth_develop_new`.`user_authentication` as `TUA` on `TUA`.`user_id` = `TU`.`id` left join `post_count_details` as `PCD` on `PCD`.`channel_userspost_id` = `CUP`.`id`
where (`CP`.`is_paid` = 'N' or (`CP`.`is_paid` = 'Y' and `CP`.`payment_status` = 'S')) and (`CP`.`channel_id` in (705, 537) or (`CUP`.`user_id` in (8, 12, 427))) and `CUP`.`updated_at` < '2019-04-12 11:09:59.000000' and ((`CP`.`channel_id` != 0 and `CM`.`channel_member_status` is not null) or `CP`.`channel_id` = 0) and ((`CP`.`post_type` != 'BV' or `CP`.`user_id` = 427) or (CP.post_type ='BV' AND EXISTS(SELECT id FROM broadcast_visibility_ids WHERE post_id=CP.id AND post_visibility='PA'))) or (CP.post_type ='BV' AND EXISTS(SELECT id FROM broadcast_visibility_ids WHERE post_id=CP.id AND post_visibility IN ('CNL_A','CRY_A')) AND EXISTS(
SELECT DISTINCT channel_members.channel_id 
FROM channel_members
INNER JOIN channels ON channels.id=channel_members.channel_id
WHERE channel_members.channel_id IN (
705,537
) AND channel_members.channel_id IN (
select channel_id from channel_members where user_id = CP.user_id AND channel_member_status = 1 AND channel_member_role = '1'
) AND channels.channel_type != 46
)) or (CP.post_type ='BV' AND EXISTS(SELECT id FROM broadcast_visibility_ids WHERE post_id=CP.id AND post_visibility IN ('CNL_A','CRY_A')) AND EXISTS(
SELECT DISTINCT channel_members.channel_id 
FROM channel_members
INNER JOIN channels ON channels.id=channel_members.channel_id
WHERE channel_members.channel_id IN (
705,537
) AND channel_members.channel_id IN (
select channel_id from channel_members where user_id = CP.user_id AND channel_member_status = 1 AND channel_member_role = '1'
) AND channels.channel_type = 46
)) or (CP.post_type ='BV' AND EXISTS(SELECT id FROM broadcast_visibility_ids WHERE post_id=CP.id AND post_visibility IN ('CNL_S','CRY_S')) AND EXISTS(
SELECT DISTINCT channel_members.channel_id 
FROM channel_members
INNER JOIN channels ON channels.id=channel_members.channel_id
WHERE channel_members.channel_id IN (
705,537
) AND channel_members.channel_id IN (
select channel_id from channel_members where user_id = CP.user_id AND channel_member_status = 1
) AND channel_members.channel_id IN (SELECT visibility_ids FROM broadcast_visibility_ids WHERE post_id=CP.id AND post_visibility IN ('CNL_S','CRY_S'))
)) order by `CUP`.`updated_at` desc limit 30


core post table's name is is channel_posts Here is the schema structure for the table 

CREATE TABLE `channel_posts` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `channel_id` bigint(20) NOT NULL,
  `rss_channel_id` int(11) NOT NULL,
  `post_title` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_content` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_type` enum('T','L','I','V','Y','G','A','MI','MV','MY','MG','MA','NS_T','NS_I','C_T','BV') COLLATE utf8mb4_unicode_ci DEFAULT 'T',
  `is_spam` enum('N','Y') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'N',
  `spam_reportedby` bigint(20) NOT NULL,
  `privacy` int(11) NOT NULL DEFAULT '2',
  `guid` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `external_url` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `link_title` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `link_content` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_breaking` enum('N','Y') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'N',
  `is_paid` enum('N','Y') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'N',
  `payment_status` enum('F','S') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'F',
  `link_image` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_live_finished` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000',
  `updated_at` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

and there is one more table channel_users_post 

CREATE TABLE `channel_users_posts` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `channel_post_id` bigint(20) NOT NULL,
  `parent_id` int(11) NOT NULL DEFAULT '0',
  `user_id` bigint(20) NOT NULL,
  `shared_from` bigint(20) NOT NULL,
  `new_title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000',
  `updated_at` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


There are 200,000 record in channel_post table and 600,000 records in channel_users_post table it takes 48586 ms to load.

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Replace Join With Exists To Avoid Redundant Grouping (modified query below): When a joined table isn’t used anywhere other than in the WHERE clause, it's equivalent to an EXISTS subquery, which often performs better. In cases where the DISTINCT or GROUP BY clause contains only columns from the Primary key, they can be removed to further improve performance, as after this transformation, they are redundant.
  2. Use Numeric Column Types For Numeric Values (query line: 163): Referencing a numeric value (e.g. 1) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
  3. Use Numeric Column Types For Numeric Values (query line: 215): Referencing a numeric value (e.g. 1) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
The optimized query:
SELECT
        CP.id,
        CP.user_id,
        post_title,
        post_content,
        `CP`.post_type,
        new_title,
        is_spam,
        `CP`.spam_reportedby,
        CP.privacy,
        CP.link_title,
        CP.link_content,
        CP.link_image,
        CP.is_paid,
        CP.payment_status,
        CP.is_breaking,
        CUP.id AS channel_userspost_id,
        CUP.parent_id,
        SU.full_name AS reporteduser_full_name,
        SU.user_name AS reporteduser_user_name,
        SU.user_profile_pic AS reporteduser_user_profile_pic,
        FU.id AS from_user_id,
        FU.full_name AS from_user_full_name,
        FU.user_name AS from_user_name,
        FU.user_profile_pic AS from_user_profile_pic,
        TU.id AS to_user_id,
        TU.full_name AS to_user_full_name,
        TU.user_name AS to_user_name,
        TU.user_profile_pic AS to_user_profile_pic,
        TUA.authentication_status AS to_user_authentication_status,
        FUA.authentication_status AS from_user_authentication_status,
        C.verification_status AS channel_verification_status,
        CUP.created_at,
        CUP.updated_at,
        guid,
        external_url,
        CP.channel_id,
        CP.rss_channel_id,
        if(CP.rss_channel_id != 0,
        RC.rss_name,
        C.channel_name) AS channel_name,
        if(CP.rss_channel_id != 0,
        RC.rss_logo,
        C.profile_pic) AS channel_logo,
        C.channel_type,
        PCD.like_count AS like_count,
        PCD.search_count AS search_count,
        PCD.view_count AS view_count,
        CM.channel_member_status,
        C.payment_status AS channel_payment_status,
        C.payment_method AS channel_payment_method,
        CP.is_live_finished 
    FROM
        `channel_users_posts` AS `CUP` 
    INNER JOIN
        `channel_posts` AS `CP` 
            ON `CUP`.`channel_post_id` = `CP`.`id` 
            AND `is_spam` = 'N' 
    LEFT JOIN
        `channels` AS `C` 
            ON `CP`.`channel_id` = `C`.`id` 
    LEFT JOIN
        `rss_channels` AS `RC` 
            ON `CP`.`rss_channel_id` = `RC`.`id` 
    LEFT JOIN
        `channel_members` AS `CM` 
            ON `CM`.`channel_id` = `C`.`id` 
            AND `CM`.`user_id` = 427 
            AND `CM`.`channel_member_status` != -1 
    LEFT JOIN
        `test_develop_new`.`users` AS `FU` 
            ON `FU`.`id` = `CUP`.`shared_from` 
    LEFT JOIN
        `test_develop_new`.`users` AS `SU` 
            ON `SU`.`id` = `CP`.`spam_reportedby` 
    LEFT JOIN
        `test_develop_new`.`users` AS `TU` 
            ON `TU`.`id` = `CUP`.`user_id` 
    LEFT JOIN
        `common_auth_develop_new`.`user_authentication` AS `FUA` 
            ON `FUA`.`user_id` = `FU`.`id` 
    LEFT JOIN
        `common_auth_develop_new`.`user_authentication` AS `TUA` 
            ON `TUA`.`user_id` = `TU`.`id` 
    LEFT JOIN
        `post_count_details` AS `PCD` 
            ON `PCD`.`channel_userspost_id` = `CUP`.`id` 
    WHERE
        (
            `CP`.`is_paid` = 'N' 
            OR (
                `CP`.`is_paid` = 'Y' 
                AND `CP`.`payment_status` = 'S'
            )
        ) 
        AND (
            `CP`.`channel_id` IN (
                705, 537
            ) 
            OR (
                `CUP`.`user_id` IN (
                    8, 12, 427
                )
            )
        ) 
        AND `CUP`.`updated_at` < '2019-04-12 11:09:59.000000' 
        AND (
            (
                `CP`.`channel_id` != 0 
                AND `CM`.`channel_member_status` IS NOT NULL
            ) 
            OR `CP`.`channel_id` = 0
        ) 
        AND (
            (
                `CP`.`post_type` != 'BV' 
                OR `CP`.`user_id` = 427
            ) 
            OR (
                CP.post_type = 'BV' 
                AND EXISTS (
                    SELECT
                        broadcast_visibility_ids.id 
                    FROM
                        broadcast_visibility_ids 
                    WHERE
                        broadcast_visibility_ids.post_id = CP.id 
                        AND broadcast_visibility_ids.post_visibility = 'PA'
                )
            )
        ) 
        OR (
            CP.post_type = 'BV' 
            AND EXISTS (
                SELECT
                    broadcast_visibility_ids.id 
                FROM
                    broadcast_visibility_ids 
                WHERE
                    broadcast_visibility_ids.post_id = CP.id 
                    AND broadcast_visibility_ids.post_visibility IN (
                        'CNL_A', 'CRY_A'
                    )
            ) 
            AND EXISTS (
                SELECT
                    DISTINCT channel_members.channel_id 
                FROM
                    channel_members 
                WHERE
                    (
                        channel_members.channel_id IN (
                            705, 537
                        ) 
                        AND channel_members.channel_id IN (
                            SELECT
                                channel_members.channel_id 
                            FROM
                                channel_members 
                            WHERE
                                channel_members.user_id = CP.user_id 
                                AND channel_members.channel_member_status = 1 
                                AND channel_members.channel_member_role = '1'
                        ) 
                        AND 1 = 1
                    ) 
                    AND (
                        EXISTS (
                            SELECT
                                1 
                            FROM
                                channels 
                            WHERE
                                (
                                    channels.id = channel_members.channel_id
                                ) 
                                AND (
                                    channels.channel_type != 46
                                )
                        )
                    )
                )
            ) 
            OR (
                CP.post_type = 'BV' 
                AND EXISTS (
                    SELECT
                        broadcast_visibility_ids.id 
                    FROM
                        broadcast_visibility_ids 
                    WHERE
                        broadcast_visibility_ids.post_id = CP.id 
                        AND broadcast_visibility_ids.post_visibility IN (
                            'CNL_A', 'CRY_A'
                        )
                ) 
                AND EXISTS (
                    SELECT
                        DISTINCT channel_members.channel_id 
                    FROM
                        channel_members 
                    WHERE
                        (
                            channel_members.channel_id IN (
                                705, 537
                            ) 
                            AND channel_members.channel_id IN (
                                SELECT
                                    channel_members.channel_id 
                                FROM
                                    channel_members 
                                WHERE
                                    channel_members.user_id = CP.user_id 
                                    AND channel_members.channel_member_status = 1 
                                    AND channel_members.channel_member_role = '1'
                            ) 
                            AND 1 = 1
                        ) 
                        AND (
                            EXISTS (
                                SELECT
                                    1 
                                FROM
                                    channels 
                                WHERE
                                    (
                                        channels.id = channel_members.channel_id
                                    ) 
                                    AND (
                                        channels.channel_type = 46
                                    )
                            )
                        )
                    )
                ) 
                OR (
                    CP.post_type = 'BV' 
                    AND EXISTS (
                        SELECT
                            broadcast_visibility_ids.id 
                        FROM
                            broadcast_visibility_ids 
                        WHERE
                            broadcast_visibility_ids.post_id = CP.id 
                            AND broadcast_visibility_ids.post_visibility IN (
                                'CNL_S', 'CRY_S'
                            )
                    ) 
                    AND EXISTS (
                        SELECT
                            DISTINCT channel_members.channel_id 
                        FROM
                            channel_members 
                        WHERE
                            (
                                channel_members.channel_id IN (
                                    705, 537
                                ) 
                                AND channel_members.channel_id IN (
                                    SELECT
                                        channel_members.channel_id 
                                    FROM
                                        channel_members 
                                    WHERE
                                        channel_members.user_id = CP.user_id 
                                        AND channel_members.channel_member_status = 1
                                ) 
                                AND channel_members.channel_id IN (
                                    SELECT
                                        broadcast_visibility_ids.visibility_ids 
                                    FROM
                                        broadcast_visibility_ids 
                                    WHERE
                                        broadcast_visibility_ids.post_id = CP.id 
                                        AND broadcast_visibility_ids.post_visibility IN (
                                            'CNL_S', 'CRY_S'
                                        )
                                )
                            ) 
                            AND (
                                EXISTS (
                                    SELECT
                                        1 
                                    FROM
                                        channels 
                                    WHERE
                                        channels.id = channel_members.channel_id
                                )
                            )
                        )
                    ) 
                ORDER BY
                    `CUP`.`updated_at` DESC LIMIT 30

Related Articles



* original question posted on StackOverflow here.