SELECT
industry.NAME AS industry_name,
count( ‘’ ) AS count
FROM
company_f5
INNER JOIN patent_proposer_relation ON patent_proposer_relation.proposer_id = company_f5.id
INNER JOIN patent ON patent_proposer_relation.patent_id = patent.id
INNER JOIN patent_industry_relation ON patent_proposer_relation.patent_id = patent_industry_relation.patent_id
INNER JOIN industry ON industry.id = patent_industry_relation.industry_id
WHERE
patent.approval_date IS NOT NULL
AND patent.type = ‘发明’
AND industry.LEVEL = 0
AND industry.has_chain = ‘是’
AND industry.has_display = ‘展示’
AND company_f5.display = 1
AND company_f5.establish_date <= ‘2024-01-12’
AND company_f5.reg_province IN ( ‘北京市’ )
AND ( industry.industry_code LIKE ‘INA%’ OR industry.industry_code LIKE ‘INT%’ )
GROUP BY
industry.NAME
ORDER BY
count( '’ ) DESC
LIMIT 15
还是把explain analyze 拉出来看看吧，从现有的预估执行计划上有一些表没用上索引，可能是没有。方便的话，可以把建表语句也拉出来看看。
例如：company_f5 表有三个条件，都没有用上索引。
AND company_f5.display = 1
AND company_f5.establish_date <= ‘2024-01-12’
AND company_f5.reg_province IN ( ‘北京市’ )
SELECT
industry.NAME AS industry_name,
COUNT(‘’) AS count
FROM
industry
INNER JOIN patent_industry_relation ON industry.id = patent_industry_relation.industry_id
INNER JOIN patent_proposer_relation ON patent_industry_relation.patent_id = patent_proposer_relation.patent_id
INNER JOIN company_f5 ON patent_proposer_relation.proposer_id = company_f5.id
INNER JOIN patent ON patent_proposer_relation.patent_id = patent.id
WHERE
industry.LEVEL = 0
AND industry.has_chain = ‘是’
AND industry.has_display = ‘展示’
AND patent.approval_date IS NOT NULL
AND patent.type = ‘发明’
AND company_f5.display = 1
AND company_f5.reg_province = ‘北京市’
AND (industry.industry_code LIKE ‘INA%’ OR industry.industry_code LIKE ‘INT%’)
AND company_f5.establish_date <= ‘2024-01-12’
GROUP BY
industry.NAME
ORDER BY
count DESC
LIMIT 15;
试试这个
tidb不是一直有么？
，TiDB还有多少功能在MySQL有之前就实现了
期待发现更多的彩蛋~