CODEWITHSUNDEEP
mysqljoinleft-join
Matthew Jonathan Brooks
Matthew Jonathan Brooks

Reputation: 11

Complex MySQL Select Left Join Optimization Indexing

I have a very complex query that is running and finding locations of members joining the subscription details and sorting by distance.

Can someone provide instruction on the correct indexes and cardinality I should add to make this load faster.

Right now on 1 million records it takes 75 seconds and I know it can be improved.

Thank you.

SELECT SQL_CALC_FOUND_ROWS (((acos(sin((33.987541*pi()/180)) * sin((users_data.lat*pi()/180))+cos((33.987541*pi()/180)) * cos((users_data.lat*pi()/180)) * cos(((-118.472153- users_data.lon)* pi()/180))))*180/pi())*60*1.1515) as distance,subscription_types.location_limit as location_limit,users_data.user_id,users_data.last_name,users_data.filename,users_data.user_id,users_data.phone_number,users_data.city,users_data.state_code,users_data.zip_code,users_data.country_code,users_data.quote,users_data.subscription_id,users_data.company,users_data.position,users_data.profession_id,users_data.experience,users_data.account_type,users_data.verified,users_data.nationwide,IF(listing_type = 'Company', company, last_name) as name
FROM `users_data`
LEFT JOIN `users_reviews` ON users_data.user_id=users_reviews.user_id AND users_reviews.review_status='2'
LEFT JOIN users_locations ON users_locations.user_id=users_data.user_id
LEFT JOIN subscription_types ON  users_data.subscription_id=subscription_types.subscription_id
WHERE users_data.active='2'
AND subscription_types.searchable='1'
AND users_data.state_code='CA'
AND users_data.country_code='US'
GROUP BY users_data.user_id
HAVING distance <= '50'
OR location_limit='all'
OR users_data.nationwide='1'
ORDER BY subscription_types.search_priority ASC, distance ASC
LIMIT 0,10

EXPLAIN

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE users_reviews system user_id,review_status NULL NULL NULL 0 const row not found 1 SIMPLE users_locations system user_id NULL NULL NULL 0 const row not found 1 SIMPLE users_data ref subscription_id,active,state_code,country_code state_code 47 const 88241 Using where; Using temporary; Using filesort 1 SIMPLE subscription_types ALL PRIMARY,searchable NULL NULL NULL 4 Using where; Using join buffer

Upvotes: 1

Views: 151

Answers (1)

Cyril Gandon
Cyril Gandon

Reputation: 17048

You query is not that complex. You have only one join, on a table subscription_types which is certainly a little table with no more than a few hundred rows.

  • Where are your indexes ? The best way to improve your query is to create indexes on the field you are filtering, like active, country_code, state_code and searchable
  • Have you create the foreign key on users_data.subscription_id ? You need an index on that too.
  • ForceIndex is useless, let the RDBMS determine the best indexes to chose.
  • Left Join is useless too, because the line subscription_types.searchable='1' will remove the unmatch correspondance
  • The order on search_priority implies that you need indexes on this columns too
  • The filtering in the HAVING can make the indexes not used. You don't need to put these filters in the HAVING. If I understand your table schema, this is not really the aggregate that is filtered.

Your table contains 1 million rows, but how much rows are returned, without the limit? With the right indexes, the query should execute under a second.

SELECT ...
FROM `users_data`
    INNER JOIN subscription_types 
        ON users_data.subscription_id = subscription_types.subscription_id 
WHERE users_data.active='2' 
  AND users_data.country_code='US' 
  AND users_data.state_code='NY'
  AND subscription_types.searchable='1'
  AND (distance <= '50' OR location_limit='all' OR users_data.nationwide='1')
GROUP BY users_data.user_id 
ORDER BY subscription_types.search_priority ASC, distance ASC 
LIMIT 0,10

Upvotes: 2

Related Questions