I am using MySQL 8.0 and there is a slow query on a large table to be optimized.
The table contains 11 million rows of data and it's structure:
CREATE TABLE `ccu` (
`id` bigint NOT NULL,
`app_id` int NOT NULL,
`ccu` int NOT NULL,
`audit_create` datetime NOT NULL,
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `ccu_game_create_time_2a10bc69_idx` (`app_id`,`audit_create`) USING BTREE,
KEY `ccu_audit_create_idx` (`audit_create`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
My query is:
SELECT app_id, DATE(audit_create) cal_day, MAX(ccu) pcu, ROUND(AVG(ccu)) id_acu
FROM ccu
WHERE audit_create BETWEEN DATE_SUB(DATE(NOW()), INTERVAL 29 DAY) AND DATE(NOW())
GROUP BY app_id, DATE(audit_create)
The query runs over 2 seconds. I add the condition by between ... and ...
to filter useful data.
However, the data stored in audit_create
is in format yyyy-MM-dd HH:mm:ss
, I have to use the date
function but according to the execution plan only the where
condition uses index(still has temporary table), the group by
clause does not use any index at all.
I have no right to alter the table structre to add a date column. Is it possible to optimize the query to lower the query time?
CodePudding user response:
I was able to eliminate the Using temporary
by adding an expression index:
mysql> alter table ccu add key bk1 (app_id, (cast(audit_create as date)));
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain SELECT app_id, DATE(audit_create) cal_day,
MAX(ccu) pcu, ROUND(AVG(ccu)) id_acu
FROM ccu
WHERE date(audit_create) BETWEEN DATE_SUB(DATE(NOW()), INTERVAL 29 DAY) AND DATE(NOW())
GROUP BY app_id, cast(audit_create as date)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: ccu
partitions: NULL
type: index
possible_keys: bk1
key: bk1
key_len: 8
ref: NULL
rows: 1
filtered: 100.00
Extra: Using where
Unfortunately, that EXPLAIN report shows it will use type: index which is an index-scan, in other words it will examine every one of the 11 million index entries. It could make it worse than it was in your query.
The only other suggestion I have is to run this query once a day and store the result in a summary table. Running a 2-second query once a day so you can get the aggregate results quickly should be acceptable. But you said you don't have authority to add a column, so I guess you don't have authority to add a table either.
In that case, get a faster computer with more RAM.