I was recommended that I ask my question here at DBA. Sorry, should that be considered “double posting”. A bit of information in advance:

I have already made the following settings (my.cnf):

```
innodb_buffer_pool_size = 8G
innodb_file_per_table = 1
innodb_log_buffer_size = 16M
innodb_log_file_size = 1G
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_sort_buffer_size = 2M
join_buffer_size = 4M
read_buffer_size = 3M
read_rnd_buffer_size = 4M
sort_buffer_size = 4M
```

Also, I now do the calculation before the “GROUP BY”. Means, I make the calculation in a `SELECT ... FROM (SELECT ... GROUP BY ...) t`

and then I use JOIN. Now to the actual topic…

I have the following 2 tables (`browsers`

and `metrics`

). `browsers`

is a “dimensions table” which stores the name and version of a browser. `metrics`

is a “facts table” which holds the `browser_id`

and metrics, in conjunction with a date. According to `explain select (...)`

no key is used on `metrics`

and the primary key is used on `browsers`

.

```
--- old query
SELECT browsers.name AS browser_name,
SUM(visits_count) AS visits_count,
SUM(clicks_count) AS clicks_count,
IFNULL((100 / SUM(visits_count)) * SUM(clicks_count), 0) AS ctr,
SUM(cost_integral) AS cost_integral,
IFNULL((SUM(cost_integral) / SUM(visits_count)), 0) AS cpv_integral,
IFNULL((SUM(cost_integral) / SUM(clicks_count)), 0) AS cpc_integral,
SUM(conversions_count) AS conversions_count,
IFNULL((100 / SUM(clicks_count)) * conversions_count, 0) AS cvr,
SUM(revenue_integral) AS revenue_integral,
IFNULL((SUM(revenue_integral) / SUM(clicks_count)), 0) AS epc_integral,
(SUM(revenue_integral) - SUM(cost_integral)) AS profit_integral,
IFNULL((SUM(revenue_integral) - SUM(cost_integral)) / SUM(cost_integral) * 100, 0) AS roi
FROM metrics
JOIN browsers ON browsers.id = browser_id
GROUP BY browsers.name
```

```
--- new query
SELECT browsers.*, `t`.*
FROM (
SELECT browser_id,
SUM(visits_count) AS visits_count,
SUM(clicks_count) AS clicks_count,
IFNULL((100 / SUM(visits_count)) * SUM(clicks_count), 0) AS ctr,
SUM(cost_integral) AS cost_integral,
IFNULL((SUM(cost_integral) / SUM(visits_count)), 0) AS cpv_integral,
IFNULL((SUM(cost_integral) / SUM(clicks_count)), 0) AS cpc_integral,
SUM(conversions_count) AS conversions_count,
IFNULL((100 / SUM(clicks_count)) * SUM(conversions_count), 0) AS cvr,
SUM(revenue_integral) AS revenue_integral,
IFNULL((SUM(revenue_integral) / SUM(visits_count)), 0) AS epv_integral,
IFNULL((SUM(revenue_integral) / SUM(clicks_count)), 0) AS epc_integral,
(SUM(revenue_integral) - SUM(cost_integral)) AS profit_integral,
ifnull((SUM(revenue_integral) - SUM(cost_integral)) / SUM(cost_integral) * 100, 0) AS roi
FROM `metrics`
GROUP BY `browser_id`
) AS `t`
INNER JOIN `browsers` ON `browsers`.`id` = `browser_id`
```

Server:

- 8 vCPU, 32 GB Memory, 250 GB SSD
- MySQL 8

Without all the SUM functions, the time of 900ms is reduced by about 250 to 300ms. Without the `GROUP BY`

even down to 1 to 2 digit ms. Unfortunately I need the `GROUP BY`

, as well as the number of SUM functions.

What can be the reason that such a server needs between 1 second and 2 seconds to execute the query on a table with only 80,000 rows? According to `explain analyze`

the SUM functions need 96% of the time (`actual time=845.038..845.052`

) that is needed in total.

```
-- browsers-Table
CREATE TABLE `browsers` (
`id` bigint(20) UNSIGNED NOT NULL,
`name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
`version` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE `browsers`
ADD PRIMARY KEY (`id`),
ADD KEY `b_n` (`name`),
ADD KEY `b_v` (`version`),
ADD KEY `b_n_v` (`name`,`version`),
ADD KEY `b_v_n` (`version`,`name`);
ALTER TABLE `browsers`
MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;
```

```
-- metrics-Table
CREATE TABLE `metrics` (
`reference_date` date NOT NULL,
`browser_id` bigint(20) UNSIGNED NOT NULL,
`visits_count` bigint(20) NOT NULL DEFAULT 0,
`cost_integral` bigint(20) NOT NULL DEFAULT 0,
`clicks_count` bigint(20) NOT NULL DEFAULT 0,
`conversions_count` bigint(20) NOT NULL DEFAULT 0,
`revenue_integral` bigint(20) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE `metrics`
ADD UNIQUE KEY `mu` (`reference_date`,`browser_id`),
ADD KEY `metrics_browser_id_foreign` (`browser_id`);
ALTER TABLE `metrics`
ADD CONSTRAINT `metrics_browser_id_foreign` FOREIGN KEY (`browser_id`) REFERENCES `browsers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
```

Even on my localserver, with the same data, I need only ~10ms – so I suspect a faulty setting of the server (according to mysqltuner there are no remarkable suggestions).

Edit #1:

```
| -> Nested loop inner join (actual time=182.931..182.937 rows=1 loops=1)
-> Table scan on landing_pages (cost=0.35 rows=1) (actual time=0.016..0.020 rows=1 loops=1)
-> Index lookup on t using <auto_key0> (landing_page_id=landing_pages.id) (actual time=0.003..0.004 rows=1 loops=1)
-> Materialize (actual time=182.911..182.912 rows=1 loops=1)
-> Table scan on <temporary> (actual time=0.001..0.002 rows=1 loops=1)
-> Aggregate using temporary table (actual time=182.830..182.830 rows=1 loops=1)
-> Index lookup on metrics using metrics_campaign_id_foreign (campaign_id=1) (cost=2065.15 rows=18004) (actual time=0.124..44.976 rows=36266 loops=1)```
```