0

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.

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

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).

0x7357
  • 13
  • 6
  • Have you checked the MySQL configuration differences between your local desktop and the server? Are they using the same engine? Cache? If the SQL command works well on a server and not in another, it's unlikely to be the SQL command's fault. – Alexis Jul 22 '21 at 00:23
  • @Alexis MariaDB is installed on my local server, whereas MySQL 8 is installed on the server. I am inexperienced in this regard, but I thought that MySQL 8 has no such differences from MariaDB. Edit: Both use the default settings (after installation). – 0x7357 Jul 22 '21 at 00:26
  • 1
    Mysql and mariadb ae two different products as mariadb fork from mysql 13 years ago. I would start with comparing execution paths. If you are interested in mysql config settigs, then DBA sister site of SO is the right choice for your question. – Shadow Jul 22 '21 at 00:58
  • From what you say this looks to be more a problem of server configuration than poor query design (although the latter is certainly possible). You migth get better answers to the server configuration issue on [dba.se] – Tangentially Perpendicular Jul 22 '21 at 01:01
  • Thanks for copying to [database adminstrators](https://dba.stackexchange.com/questions/296049/8-vcpu-32-gb-memory-250-gb-ssd-server-needs-1-to-2-seconds-for-a-query-with-12) – danblack Jul 22 '21 at 23:44

0 Answers0