How to improve a mysql COUNT query for speed?-CodePudding

How can I improve this query for speed? at the moment it's taking a couple of seconds only to load the php file where the query is without even querying anything.

I've an index on skillsTrends, jobtitle and industry. Collation: utf8mb4_unicode_ci

$sql = "SELECT 
   COUNT(skillsTrends), 
   skillsTrends,
   jobtitle,
   industry,
   industry_url 
FROM fr_skills_trends 
WHERE industry IN ('". implode("', '", $industryInsertSql). "') 
  AND LENGTH(skillsTrends)<=35 
  AND reg_date >= NOW() - INTERVAL 3 MONTH 
GROUP BY skillsTrends ORDER by LENGTH(skillsTrends) DESC";

Number of records < 1,000,000.

CodePudding user response：

Try this covering index.

CREATE INDEX fr_skills_trends_date_industry 
          ON fr_skills_trends 
             (reg_date , industry, skillsTrends);

It should help the performance of your query.

And, your query misuses MySQL's notorious nonstandard extension to GROUP BY. Try this instead.

SELECT 
   COUNT(skillsTrends), 
   skillsTrends,
   jobtitle,
   industry,
   industry_url 
FROM fr_skills_trends 
WHERE industry IN ('". implode("', '", $industryInsertSql). "') 
  AND LENGTH(skillsTrends)<=35 
  AND reg_date >= NOW() - INTERVAL 3 MONTH 
GROUP BY skillsTrends, jobtitle, industry, industry_url
ORDER by LENGTH(skillsTrends) DESC