CODEWITHSUNDEEP
mysqlsql
0x7357
0x7357

Reputation: 13

What makes this query so slow? 150,000 affected rows take more than 1.5 seconds

I have been stuck on this query for more than 2 days. I already tried sub-queries (in SELECT), I already tried "join everything with LEFT JOIN and count(column.id) (NULL = 0)".

Campaigns Table:

CREATE TABLE `campaigns` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `campaigns`
  ADD PRIMARY KEY (`id`);
  
ALTER TABLE `campaigns`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

Operating Systems Table:

CREATE TABLE `operating_systems` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `version` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `operating_systems`
  ADD PRIMARY KEY (`id`),
  ADD KEY `operating_systems_name_index` (`name`),
  ADD KEY `operating_systems_version_index` (`version`),
  ADD KEY `operating_systems_name_version_index` (`name`,`version`);

ALTER TABLE `operating_systems`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

Clicks Table:

CREATE TABLE `clicks` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `campaign_id` bigint(20) UNSIGNED NOT NULL,
  `operating_system_id` bigint(20) UNSIGNED NOT NULL,
  `cost_integral` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `clicks`
  ADD PRIMARY KEY (`id`),
  ADD KEY `c_c_ca` (`campaign_id`,`created_at`),
  ADD KEY `c_c_os_ca` (`campaign_id`,`operating_system_id`,`created_at`);

ALTER TABLE `clicks`
  ADD CONSTRAINT `c_c` FOREIGN KEY (`campaign_id`) REFERENCES `campaigns` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `c_os` FOREIGN KEY (`operating_system_id`) REFERENCES `operating_systems` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `clicks`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

Conversions Table:

CREATE TABLE `conversions` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `click_id` bigint(20) UNSIGNED NOT NULL,
  `payout_integral` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `conversions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `conversions_created_at_index` (`created_at`),
  ADD KEY `conversions_click_id_created_at_index` (`click_id`,`created_at`);

ALTER TABLE `conversions`
  ADD CONSTRAINT `conversions_click_id_foreign` FOREIGN KEY (`click_id`) REFERENCES `clicks` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `conversions`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

Slow Query:

select operating_systems.name,
    ifnull(sum(metrics.clicks_count), 0) as clicks_count,
    ifnull(sum(metrics.conversions_count), 0) as conversions_count,
    ifnull(round(((100 / sum(metrics.clicks_count)) * sum(metrics.conversions_count)), 2), 0) as conversion_rate,
    cast(ifnull(sum(metrics.cost_integral), 0) as unsigned) as cost_integral,
    cast(ifnull((sum(metrics.cost_integral) / sum(metrics.clicks_count)), 0) as unsigned) as cpc_integral,
    cast(ifnull(sum(metrics.revenue_integral), 0) as unsigned) as revenue_integral,
    cast(ifnull((ifnull(sum(metrics.revenue_integral), 0) / sum(metrics.clicks_count)), 0) as unsigned) as epc_integral,
    cast((ifnull(sum(metrics.revenue_integral), 0) - ifnull(sum(metrics.cost_integral), 0)) as signed) as profit_integral,
    round((
            case 
                when (ifnull(sum(metrics.revenue_integral), 0) = 0 and ifnull(sum(metrics.cost_integral), 0) = 0)
                    then 0
                when (ifnull(sum(metrics.revenue_integral), 0) = 0 and ifnull(sum(metrics.cost_integral), 0) > 0)
                    then - 100
                when (ifnull(sum(metrics.revenue_integral), 0) > 0 and ifnull(sum(metrics.cost_integral), 0) = 0)
                    then 100
                when (ifnull(sum(metrics.revenue_integral), 0) > 0 and ifnull(sum(metrics.cost_integral), 0) > 0)
                    then ((sum(metrics.revenue_integral) / sum(metrics.cost_integral)) * 100)
                end
            ), 2) as roi
from operating_systems
left join (
    select c1.operating_system_id as operating_system_id,
        count(c1.id) as clicks_count,
        sum(c1.cost_integral) as cost_integral,
        count(c2.id) as conversions_count,
        sum(c2.payout_integral) as revenue_integral
    from clicks as c1
    left join conversions as c2 on c2.click_id = c1.id
    where c1.campaign_id = '2' and c1.created_at >= '2021-07-06 00:00:00' and c1.created_at <= '2021-07-14 23:59:59'
    group by c1.operating_system_id
    ) as metrics on operating_systems.id = metrics.operating_system_id
group by operating_systems.name;

Explain:

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 PRIMARY operating_systems NULL index operating_systems_name_index,operating_systems_name_version_index operating_systems_name_index 1022 NULL 12 100.00 Using index
1 PRIMARY NULL ref <auto_key0> <auto_key0> 8 dn_tracker.operating_systems.id 777 100.00 NULL
2 DERIVED c1 NULL ref c_os,c_ca,c_c_v,c_c_ca,c_c_b_ca,c_c_os_ca c_c_os_ca 8 const 155472 50.00 Using index condition
2 DERIVED c2 NULL ref conversions_click_id_created_at_index conversions_click_id_created_at_index 8 dn_tracker.c1.id 1 100.00 NULL

Profiling:

Query_ID Duration Query
1 1.49035375 select operating_systems.name, ifnull(sum(metrics.clicks_count), 0) as clicks_count, ifnull(sum(metrics.conversions_count), 0) as conversions_count, ifnull(round(((100 / sum(metrics.clicks_count)) * sum(metrics.conversions_count)), 2), 0) as conversion_rate, cast(ifnull(sum(metri

What could be the reason that this query, with "only" ~150,000 rows, takes between 1.5 and 6 seconds? I think I created the right indexes etc., so I don't understand why this query takes so long on a server with 12 CPUs (Dedicated) and 16 GB RAM.

Upvotes: 0

Views: 125

Answers (1)

Rick James
Rick James

Reputation: 142518

Build and maintain a summary table. Add the day's data to the summary table after midnight each night. Then rewrite this "report" to work from the summary table.

More details: http://mysql.rjweb.org/doc.php/summarytables

In some cases, a summary table will speed 'reports' up by 10-fold.

Also, this may help:

conversions: INDEX(click_id, payout_integral)

MySQL has essentially no parallel execution.

Upvotes: 0

Related Questions