sql - MySQL is not using prmary index -

i have query:

select sql_no_cache     count(*) `numrows`     (`citations`)         left join     `projects` on `projects`.`project_id` = `citations`.`project_id`         left join     `users` on `users`.`user_id` = `projects`.`user_id`     `users`.`role` = '0'         , `citations`.`created` between 1360213200 , 1360299599         , `citations`.`in_card` = '0'         , `citations`.`citation_id` not in (select              user_stats_citations.citation_id                     user_stats_citations,             user_stats force index (user_stats_type_index)                     user_stats_citations.user_stat_id = user_stats.id                 , user_stats.type in (69 , 70, 71, 75, 76)); 

i have indexes on user table:

users            0  primary                             1  user_id                     42836    (null)  (null)          btree                                users            1  users_industry_id_index             1  industry_id                   118    (null)  (null)  yes     btree                                users            1  users_sponsor_index                 1  sponsor                        12    (null)  (null)  yes     btree 

this output of explain extended

  id    select_type table   type    possible_keys   key key_len ref rows    filtered       1 primary users   primary \n  \n  \n  42836   100.00  using   1 primary projects    ref primary\,projects_user_id_index projects_user_id_index  4   citelighter.users.user_id   1   100.00  using where; using index   1 primary citations   ref citations_project_id_index  citations_project_id_index  4   citelighter.projects.project_id 4   100.00  using index condition; using   2 subquery    user_stats  range   user_stats_type_index   user_stats_type_index   2   \n  410768  100.00  using where; using index   2 subquery    user_stats_citations    ref user_stats_citations_index_user_stat_id\,user_stats_citations_index_citation_id user_stats_citations_index_user_stat_id 8   citelighter.user_stats.id   1   100.00  \n 

i tried add force index on users left join index not used. can me solve this, because query taking 10 seconds on local , 1 second on production environment.

the first thing notice predicate in clause: where users.role = '0' turns left joins inner joins, may make them inner joins.

secondly, mysql has problems optimising correlated subqueries, , can perform poorly derived tables. e.g. in this simple query:

select * (select * t) t join (select * t) t2 on t.id = t2.id; 

even though id primary key on t, primary key not used join can't cascaded out of derived table. when write:

select * t afield not in (select afield t anotherfield = 1); 

mysql not materialise subquery , use this, rewrite query as:

select * t not exists (select 1                      t t2                      t.afield = t2.afield                       , t2.anotherfield = 1); 

and subquery executed each row in outer query, if have large number of rows in outer query executing subquery every row becomes costly. solution avoid subqueries far possible. in case can rewrite query as:

select  sql_no_cache         count(*) `numrows`    `citations`         inner join `projects`              on `projects`.`project_id` = `citations`.`project_id`         inner join `users`              on `users`.`user_id` = `projects`.`user_id`         left join (user_stats_citations             inner join user_stats                 on user_stats_citations.user_stat_id = user_stats.id                 , user_stats.type in (69 , 70, 71, 75, 76))             on user_stats_citations.citation_id = `citations`.`citation_id`   `users`.`role` = '0' ,     `citations`.`created` between 1360213200 , 1360299599 ,     `citations`.`in_card` = '0' ,      user_stats_citations.citation_id null; 

with no subqueries there no derived tables, or row row execution of subqueries. should improve execution time.