CODEWITHSUNDEEP
mysql
Sarath
Sarath

Reputation: 93

Optimize the query with index

MySQL query taking 1.6 seconds for 40000 records in table

 SELECT aggsm.topicdm_id AS topid,citydm.city_name
 FROM AGG_MENTION AS aggsm
 JOIN LOCATIONDM AS locdm ON aggsm.locationdm_id = locdm.locationdm_id
 JOIN CITY AS citydm ON locdm.city_id = citydm.city_id
 JOIN STATE AS statedm ON citydm.state_id = statedm.state_id
 JOIN COUNTRY AS cntrydm ON statedm.country_id = cntrydm.country_id
 WHERE  cntrydm.country_id IN (1,2,3,4)
 GROUP BY aggsm.topicdm_id,aggsm.locationdm_id
 LIMIT 0,200000

I have 40000 to 50000 records in AGG_MENTION,LOCATIONDM,CITYDM tables....500records in STATEDM abd 4 records in COUNTRY table. When i run above query it is taking 1.6 sec..Is there a way to optimize the query or index on which columns will improve the performance.... Following is the EXPLAIN output:

   1    SIMPLE  aggsm   index   agg_sm_locdm_fk_idx agg_sm_datedm_fk_idx    4       36313   Using index; Using temporary; Using filesort
   1    SIMPLE  locdm   eq_ref  PRIMARY,city_id_UNIQUE,locationdm_id_UNIQUE,loc_city_fk_idx PRIMARY 8   opinionleaders.aggsm.locationdm_id  1   
   1    SIMPLE  citydm  eq_ref  PRIMARY,city_id_UNIQUE,city_state_fk_idx    PRIMARY 8   opinionleaders.locdm.city_id    1   
   1    SIMPLE  statedm eq_ref  PRIMARY,state_id_UNIQUE,state_country_fk_idx    PRIMARY 8   opinionleaders.citydm.state_id  1   Using where
   1    SIMPLE  cntrydm eq_ref  PRIMARY,country_id_UNIQUE   PRIMARY 8   opinionleaders.statedm.country_id   1   Using index

Upvotes: 0

Views: 58

Answers (1)

DRapp
DRapp

Reputation: 48169

I would reverse the query and start with the STATE first as that is what your criteria is based upon. Since you are not actually doing anything with the country table (except the country ID)... This column also exists in the State table, so you can the State.Country_ID and remove the country table from the join.

Additionally, I would have the following indexes

Table         Index
State         (Country_ID) as that will be basis of your WHERE criteria.  
City          (State_ID, City_Name).  
Location      (City_ID)
Agg_Mention   (LocationDM_ID, TopicDM_id).

By having the "City_Name" as part of the index, the query doesn't have to go to the actual page data for it. Since part of the index, it can use it directly.

Many times, the keyword "STRAIGHT_JOIN" included here helps optimizer to run query in the order stated so it doesn't try to take one of the other tables as its primary basis of querying the data. If that doesn't perform well, you can try it again without it.

SELECT STRAIGHT_JOIN
      aggsm.topicdm_id AS topid,
      citydm.city_name
   FROM 
      STATE AS statedm 
         JOIN CITY AS citydm 
            ON statedm.state_id = citydm.state_id
               JOIN LOCATIONDM AS locdm 
                  ON citydm.city_id = locdm.city_id 
                     join AGG_MENTION AS aggsm
                        ON locdm.locationdm_id = aggsm.locationdm_id 
   WHERE
      statedm.country_id IN (1,2,3,4)
   GROUP BY 
      aggsm.topicdm_id,
      aggsm.locationdm_id
   LIMIT 0,200000

Upvotes: 1

Related Questions