mysql count 大于_mysql-SELECT COUNT具有大于100M行的表的JOIN优化

我有以下查询

SELECT SUBSTRING(a0_.created_date FROM 1 FOR 10) AS sclr_0,

COUNT(1) AS sclr_1

FROM applications a0_ INNER JOIN

package_codes p1_ ON a0_.id = p1_.application_id

WHERE a0_.created_date BETWEEN '2019-01-01' AND '2020-01-01' AND

p1_.type = 'Package 1'

GROUP BY sclr_0

-编辑-

你们大多数人都专注于GROUP BY和SUBSTRING,但这不是问题的根源.

以下查询具有相同的执行时间：

SELECT COUNT(1) AS sclr_1

FROM applications a0_ INNER JOIN

package_codes p1_ ON a0_.id = p1_.application_id

WHERE a0_.created_date BETWEEN '2019-01-01' AND '2020-01-01' AND

p1_.type = 'Package 1'

-编辑2-

在application.created_date上添加索引并强制查询使用指定的索引作为@DDS后,建议执行时间降至750ms

当前查询如下：

SELECT SUBSTRING(a0_.created_date FROM 1 FOR 10) AS sclr_0,

COUNT(1) AS sclr_1

FROM applications a0_ USE INDEX (applications_created_date_idx) INNER JOIN

package_codes p1_ USE INDEX (PRIMARY, UNIQ_70A9C6AA3E030ACD, package_codes_type_idx) ON a0_.id = p1_.application_id

WHERE a0_.created_date BETWEEN '2019-01-01' AND '2020-01-01' AND

p1_.type = 'Package 1'

GROUP BY sclr_0

-编辑3-

我发现在查询中使用太多索引可能会导致在某些情况下MySQL将使用非最佳索引,因此最终查询应如下所示：

SELECT SUBSTRING(a0_.created_date FROM 1 FOR 10) AS sclr_0,

COUNT(1) AS sclr_1

FROM applications a0_ USE INDEX (applications_created_date_idx) INNER JOIN

package_codes p1_ USE INDEX (package_codes_application_idx) ON a0_.id = p1_.application_id

WHERE a0_.created_date BETWEEN '2019-01-01' AND '2020-01-01' AND

p1_.type = 'Package 1'

GROUP BY sclr_0

—结束编辑—

package_codes包含超过100.000.000条记录.

应用程序包含超过250.000条记录.

查询需要2分钟才能得到结果.有什么方法可以优化它吗？

我坚持使用MySQL 5.5.

表格：

CREATE TABLE `applications` (

`id` int(11) NOT NULL,

`created_date` datetime NOT NULL,

`name` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,

`surname` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `applications`

ADD PRIMARY KEY (`id`),

ADD KEY `applications_created_date_idx` (`created_date`);

ALTER TABLE `applications`

MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

CREATE TABLE `package_codes` (

`id` int(11) NOT NULL,

`application_id` int(11) DEFAULT NULL,

`created_date` datetime NOT NULL,

`type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,

`code` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,

`disabled` tinyint(1) NOT NULL DEFAULT '0',

`meta_data` longtext COLLATE utf8mb4_unicode_ci

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `package_codes`

ADD PRIMARY KEY (`id`),

ADD UNIQUE KEY `UNIQ_70A9C6AA3E030ACD` (`application_id`),

ADD KEY `package_codes_code_idx` (`code`),

ADD KEY `package_codes_type_idx` (`type`),

ADD KEY `package_codes_application_idx` (`application_id`),

ADD KEY `package_codes_code_application_idx` (`code`,`application_id`);

ALTER TABLE `package_codes`

MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `package_codes`

ADD CONSTRAINT `FK_70A9C6AA3E030ACD` FOREIGN KEY (`application_id`) REFERENCES `applications` (`id`);