I have this query:
SELECT
count( * ) AS AGGREGATE
FROM
`report_ifind`
INNER JOIN `report_descriptions` ON `report_ifind`.`seq` =
`report_descriptions`.`report_seq`
AND `description` IS NOT NULL
WHERE
EXISTS (
SELECT
1
FROM
`report_types`
WHERE
report_ifind.seq = report_types.report_seq
AND `type_code` NOT IN ( '002', '001001', '001002', '001003', '099001002',
'009001001', '003001', '003002', '003003', '004002' )
)
AND `isvalid` = 1
AND `report_ifind`.`publish_time` >= '2017-06-24'
There is three table in this query:
report_ifind
report_descriptions
report_types
The index of those table:
**report_ifind**:
PRIMARY KEY (`id`),
UNIQUE KEY `idx_seq` (`seq`),
KEY `idx_research_id` (`researcher_id`),
KEY `idx_org_id` (`org_id`),
KEY `idx_publish_time` (`publish_time`) USING BTREE,
KEY `idx_sql_publish_time` (`publish_time`,`seq`) USING BTREE
**report_descriptions**:
PRIMARY KEY (`id`),
KEY `idx_report_seq` (`report_seq`)
**report_types**:
PRIMARY KEY (`id`),
KEY `idx_report_seq` (`report_seq`),
KEY `idx_type_code` (`type_code`)
I know something is bad:
1、 " desctiption is not null " is slow
2、 " WHERE EXIST () " is slow
3、 " SELECT COUNT(*) " is slow
How to optimize it?