CODEWITHSUNDEEP
phpmysql
Rupal Javiya
Rupal Javiya

Reputation: 579

Query performance issue

am working with mySql, and with below query am getting performance issue:

SELECT COUNT(*)
FROM
  (SELECT company.ID
   FROM `company`
   INNER JOIN `featured_company` ON (company.ID=featured_company.COMPANY_ID)
   INNER JOIN `company_portal` ON (company.ID=company_portal.COMPANY_ID)
   INNER JOIN `job` ON company.ID = job.COMPANY_ID
   WHERE featured_company.DATE_START<='2016-09-21'
     AND featured_company.DATE_END>='2016-09-21'
     AND featured_company.PORTAL_ID=16
     AND company_portal.PORTAL_ID=16
     AND (company.IMAGE IS NOT NULL
          AND company.IMAGE<>'')
     AND job.IS_ACTIVE=1
     AND job.IS_DELETED=0
     AND job.EXPIRATION_DATE >= '2016-09-21'
     AND job.ACTIVATION_DATE <= '2016-09-21'
   GROUP BY company.ID)

with this query am getting below newrelic log (Query analysis: Table - Hint):

featured_company 

- The table was retrieved with this index: portal_date_start_end
- A temporary table was created to access this part of the query, which can cause poor performance. This typically happens if the query contains GROUP BY and ORDER BY clauses that list columns differently.
- MySQL had to do an extra pass to retrieve the rows in sorted order, which is a cause of poor performance but sometimes unavoidable.
- You can speed up this query by querying only fields that are within the index. Or you can create an index that includes every field in your query, including the primary key.
Approximately 89 rows of this table were scanned.

company_portal 

- The table was retrieved with this index: PRIMARY
- Approximately 1 row of this table was scanned.

job 

- The table was retrieved with this index: company_expiration_date
- You can speed up this query by querying only fields that are within the index. Or you can create an index that includes every field in your query, including the primary key.
- Approximately 37 rows of this table were scanned.

company 

- The table was retrieved with this index: PRIMARY
- You can speed up this query by querying only fields that are within the index. Or you can create an index that includes every field in your query, including the primary key.
- Approximately 1 row of this table was scanned.

I don't get idea what more I can do for this query optimization, please provide ideas if you have

Upvotes: 1

Views: 166

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133400

Be sure you have proper index on :

  featured_company.DATE_START
  featured_company.PORTAL_ID

  job.IS_ACTIVE
  job.IS_DELETED
  job.EXPIRATION_DATE
  job.ACTIVATION_DATE

and eventually company.IMAGE

Assuming that the id are already indexed 

  company.ID
  featured_company.COMPANY_ID     
  job.COMPANY_ID

and a suggestion based on the fact you don't use aggregation function don't use group by use DISTINCT instead 

  company.ID
  featured_company.COMPANY_ID     
  job.COMPANY_ID 

SELECT COUNT(*) FROM (
  SELECT DISTINCT company.ID 
  FROM `company` 
  INNER JOIN `featured_company` ON company.ID=featured_company.COMPANY_ID
  INNER JOIN `company_portal`   ON company.ID=company_portal.COMPANY_ID
  INNER JOIN `job`              ON company.ID = job.COMPANY_ID 
  WHERE featured_company.DATE_START<='2016-09-21' 
  AND featured_company.DATE_END>='2016-09-21' 
  AND featured_company.PORTAL_ID=16 
  AND company_portal.PORTAL_ID=16
  AND (company.IMAGE IS NOT NULL AND company.IMAGE<>'') 
  AND job.IS_ACTIVE=1 
  AND job.IS_DELETED=0 
  AND job.EXPIRATION_DATE >= '2016-09-21' 
  AND job.ACTIVATION_DATE <= '2016-09-21' 
)

Upvotes: 1

Related Questions