MYSQL – How to optimize VIEW performance

MySQLmysql-5.7order-byrdbmsview

  • Total profiles – 100000
  • Each profile is connected to one or more platform
  • Each profile have 50 Tags
  • Each profile have 4 themes

I have a user view (total users around 100000) where I am joining all the user data and getting it at once place. My current view query is given below:

SELECT      (fields to select)
FROM        profiles
LEFT JOIN   profile_calculated_totals ON (profiles.id = profile_calculated_totals.profile_id)
LEFT JOIN   platform_one ON ((platform_one.private = 'N') AND(profiles.platform_one = platform_one.id))
LEFT JOIN   platform_two ON (profiles.platform_two = platform_two.id)
LEFT JOIN   platform_three ON (profiles.platform_three = platform_three.id)
LEFT JOIN   (
        SELECT      profiles_tags.profile_id, GROUP_CONCAT(tags.tag) as profile_tags
        FROM        profiles_tags
        JOIN        tags ON(profiles_tags.tag_id = tags.id)
        GROUP BY    profiles_tags.profile_id
)as X ON (profiles.id = X.profile_id)
LEFT JOIN   (
        SELECT      profiles_themes.profile_id, GROUP_CONCAT(themes.name) as profile_themes
        FROM        profiles_themes
        JOIN        themes ON(profiles_themes.theme_id = themes.id)
        GROUP BY    profiles_themes.profile_id
)as Y ON (profiles.id = Y.profile_id)
WHERE       profiles.verified = 'Y'

EXPLAIN result
enter image description here

If I will run the view or this raw query it takes around 2-3 seconds to load. SELECT * FROM profile_view LIMIT 0,50

To optimize query I did some research and found out about the VIEW ALGOs TEMPTABLE and MERGE + I did EXPLAIN to the query and found out there are lot of derived tables, so I rewrite the query as below: 

SELECT      (fields to select)
FROM        profiles
LEFT JOIN   profile_calculated_totals ON (profiles.id = profile_calculated_totals.profile_id)
LEFT JOIN   platform_one ON ((platform_one.private = 'N') AND(profiles.platform_one = platform_one.id))
LEFT JOIN   platform_two ON (profiles.platform_two = platform_two.id)
LEFT JOIN   platform_three ON (profiles.platform_three = platform_three.id)
LEFT JOIN   profiles_tags   ON(profiles.id = profiles_tags.profile_id)
LEFT JOIN   tags            ON(profiles_tags.tag_id = tags.id)
LEFT JOIN   profiles_themes ON(profiles.id = profiles_themes.profile_id)
LEFT JOIN   themes          ON(profiles_themes.theme_id = themes.id)
WHERE       (profiles.verified = 'Y')
GROUP BY    profiles.id
ORDER BY    profile_calculated_totals.total_followers DESC

EXPLAIN result
enter image description here
If I will run this query, it's very fast and showing the results 0.5 seconds but when I will apply ORDER BY with this query it's taking 10-12 Seconds (here I have already created index for column profile_id)

Now, one of the solutions I am thinking is saving the profile tags as comma separated in DB(this is solving all the problems speed and sort both) but I have seen lot of posts on Internet that it's not RDBMS way and will create problems in future.

The Table structures involved in this are given below:
profiles ->

`CREATE TABLE `profiles` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`slug` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`gender` enum('male','female','transgender') CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
`url` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`email` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`audience` enum('male','female','both') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`birthday` date DEFAULT NULL,
`age_min` int(2) DEFAULT NULL,
`age_max` int(2) DEFAULT NULL,
`phone` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`address_1` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
`address_city` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`bio` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
`country` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`continent` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`languages` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`platform_two` bigint(20) unsigned DEFAULT NULL,
`platform_one` bigint(20) unsigned DEFAULT NULL,
`platform_three` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`score` int(2) unsigned NOT NULL DEFAULT '0',
`verified` enum('Y','N') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'Y',
`kind` enum('influencer','brand') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'influencer',
`sub_kind` enum('blogger','platform_oneer','platform_threer','model','artist','celebrity','magazine') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`blogger_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`agent_address` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
`agent_city` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`agent_country` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`agent_continent` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`agent_phone` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`agent_email` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`salesforce_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `slug` (`slug`),
UNIQUE KEY `platform_one` (`platform_one`),
UNIQUE KEY `platform_two` (`platform_two`),
UNIQUE KEY `platform_three` (`platform_three`)
) ENGINE=InnoDB AUTO_INCREMENT=34985 DEFAULT CHARSET=utf8mb4 
COLLATE=utf8mb4_unicode_ci

platform_one – 

CREATE TABLE `platform_one` (
`id` bigint(20) unsigned NOT NULL,
`username` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`full_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`website` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
`bio` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
`media` int(10) DEFAULT NULL,
`followed_by` int(10) DEFAULT NULL,
`follows` int(10) DEFAULT NULL,
`profile_picture` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
`is_verified` tinyint(1) DEFAULT '0',
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
CONSTRAINT `platform_one_id_profiles_platform_one` FOREIGN KEY (`id`) 
REFERENCES `profiles` (`platform_one`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

platform_two ->

CREATE TABLE `platform_two` (
 `id` bigint(20) unsigned NOT NULL,
 `username` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `picture` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
 `about` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
 `birthday` date DEFAULT NULL,
 `category` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `likes` bigint(20) DEFAULT NULL,
 `engagement_rate` decimal(6,2) DEFAULT NULL,
 `media_value` decimal(20,2) DEFAULT NULL,
 `growth` decimal(4,2) DEFAULT NULL,
 `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
 `link` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
 `website` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
 `release_date` date DEFAULT NULL,
 `general_info` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
 `personal_info` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
 `bio` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
 `personal_interests` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
 `phone` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 CONSTRAINT `profiles_platform_two_platform_two_id` FOREIGN KEY (`id`) REFERENCES `profiles` (`platform_two`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

platform_three ->

CREATE TABLE `platform_three` (
 `id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
 `name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `picture` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
 `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
 `upload_playlist_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
 `view_count` int(10) unsigned DEFAULT NULL,
 `comment_count` int(10) unsigned DEFAULT NULL,
 `subscriber_count` int(10) unsigned DEFAULT NULL,
 `video_count` int(10) unsigned DEFAULT NULL,
 `engagement_rate` decimal(6,2) DEFAULT NULL,
 `media_value` decimal(20,2) DEFAULT NULL,
 `growth` decimal(4,2) DEFAULT NULL,
 `published_at` int(10) unsigned DEFAULT NULL,
 `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 KEY `upload_playlist_id` (`upload_playlist_id`),
 KEY `engagement_rate` (`engagement_rate`),
 CONSTRAINT `profiles_platform_three_platform_three_id` FOREIGN KEY (`id`) REFERENCES `profiles` (`platform_three`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

profile_calculated_totals ->

CREATE TABLE `profile_calculated_totals` (
 `profile_id` int(11) NOT NULL,
 `total_followers` bigint(20) NOT NULL COMMENT 'total followers of the profile',
 `total_eg_rate` decimal(12,6) NOT NULL COMMENT 'total EG rate of the profile',
 `total_growth` decimal(12,6) NOT NULL COMMENT 'total Growth of the profile',
 PRIMARY KEY (`profile_id`),
 KEY `total_followers` (`total_followers`),
 KEY `total_eg_rate` (`total_eg_rate`),
 KEY `total_growth` (`total_growth`),
 CONSTRAINT `profile_calculated_totals_profile_id_profiles_id` FOREIGN KEY (`profile_id`) REFERENCES `profiles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='table contains the total calculated values for the profile'

profiles_tags ->

CREATE TABLE `profiles_tags` (
 `profile_id` int(11) NOT NULL,
 `tag_id` int(11) unsigned NOT NULL,
 `occurrences` int(11) NOT NULL,
 PRIMARY KEY (`profile_id`,`tag_id`),
 KEY `tag_id` (`tag_id`),
 CONSTRAINT `profiles_tags_profile_id_profiles_id` FOREIGN KEY (`profile_id`) REFERENCES `profiles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `profiles_tags_tag_id_tags_id` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

tags -> 

CREATE TABLE `tags` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `tag` varchar(190) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
 `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 UNIQUE KEY `hashtag` (`tag`)
) ENGINE=InnoDB AUTO_INCREMENT=700840 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

profiles_themes ->

CREATE TABLE `profiles_themes` (
 `profile_id` int(11) NOT NULL,
 `theme_id` int(11) NOT NULL,
 PRIMARY KEY (`profile_id`,`theme_id`),
 KEY `profiles_themes_theme_id_themes_id` (`theme_id`),
 CONSTRAINT `profiles_themes_profile_id_profiles_id` FOREIGN KEY (`profile_id`) REFERENCES `profiles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `profiles_themes_theme_id_themes_id` FOREIGN KEY (`theme_id`) REFERENCES `themes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

themes ->

CREATE TABLE `themes` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
 `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Best Answer

If that is the VIEW, let's see a SELECT (or whatever) using it.

A common optimization is to avoid LEFT JOIN (SELECT..) thus:

SELECT ... profile_tags ...
    ...
    LEFT JOIN  
    (
        SELECT  profiles_tags.profile_id, GROUP_CONCAT(tags.tag) as profile_tags
            FROM  profiles_tags
            JOIN  tags ON(profiles_tags.tag_id = tags.id)
            GROUP BY  profiles_tags.profile_id
    ) as X  ON profiles.id = X.profile_id

-->

SELECT ...
    ( SELECT GROUP_CONCAT(tags.tag)
            FROM  profiles_tags AS pt
            JOIN  tags ON(pt.tag_id = tags.id)
            WHERE profiles.id = pt.profile_id
    ) as profile_tags
    ...
    (and remove the LEFT JOIN)

Ditto for profile_themes

Many-to-many tables are costly because of this 2-hop you have to do. It may be better, in the long run, to have the tag in the many-to-many table and get rid of the table tags.

Then it becmes

SELECT ...
    ( SELECT GROUP_CONCAT(tag)
            FROM  profiles_tags
            WHERE profiles.id = profile_id
    ) as profile_tags
    ...
    (and remove the LEFT JOIN)
Related Question