Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This query comes in slow query log when log_queries_not_using_indexes is enabled.

SELECT a.id, d.id AS city_id, d.city
FROM booking a, sub_area_master b, area_master c, city_master d
WHERE a.sub_area_id = b.id
AND b.area_id = c.id
AND d.id = c.city_id
AND a.user_id =  '123'
ORDER BY a.id DESC 
LIMIT 1

Indexes exists as follow

booking table

PRIMARY BTREE   id
user_id BTREE   user_id
sub_area_id BTREE   sub_area_id
status_id   BTREE   status_id

area_master table

PRIMARY BTREE   id  
city_id BTREE   city_id

sub_area_master table

PRIMARY BTREE   id
area_id BTREE   area_id
cds_id  BTREE   cds_id
sub_area    BTREE   sub_area

city_master table

PRIMARY BTREE   id
city    BTREE   city

Here is the EXPLAIN for the same.

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  a   ref user_id,sub_area_id user_id 4   const   76  Using temporary; Using filesort
1   SIMPLE  b   eq_ref  PRIMARY,area_id PRIMARY 4   a.sub_area_id   1   
1   SIMPLE  c   eq_ref  PRIMARY,city_id PRIMARY 4   b.area_id   1   
1   SIMPLE  d   ALL PRIMARY NULL    NULL    NULL    6   Using where; Using join buffer

please suggest what to do to avoid this query in slow query log.

share|improve this question
    
MyISAM or InnoDB tables? –  ypercube Feb 27 at 10:38
    
@ypercube, All are MyISAM tables. –  user3003994 Feb 27 at 12:07
    
Can you test the execution plan after you add an (user_id, id) index in the a table? –  ypercube Feb 27 at 12:21
    
The query scans city_master . If you enable log_queries_not_using_indexes the query will go to the slow log. All works as expected - what's the problem? –  akuzminsky Feb 27 at 14:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.