mysql - Why isn't the index used? -

select `nen straatnaam` street, `nen woonplaats` city, gemeente,    postcode, acn_distinct.zipcodes, acn_distinct.lat, acn_distinct.lng   `acn_distinct` inner join crimes c    on `nen woonplaats` = c.place , c.street_check = 0  order street asc 

explain gives me information:

id  select_type table           type    possible_keys                               key                 key_len ref             rows    1   simple      c               ref     idx_place,idx_street_check,fulltext_place   idx_street_check    1       const           67556   using temporary; using filesort 1   simple      acn_distinct    ref     id_nen_woonplaats                           id_nen_woonplaats   768     crimes.c.place  42      using index condition 

so why not using suggested indexes?

it using index, idx_street_check, however, performance terrible, since it's creating temporary table , using filesort both notorious culprits.

the question why it's not using index on crimes.place. try creating multi-column index on (place, street_check).

but more importantly, think table schema very, bad. join poorly formed:

from  `acn_distinct` inner join crimes c    on `nen woonplaats` = c.place , c.street_check = 0  

when join between tables , b, should join such: a.x = b.y. in case you're not referring table a. you're multiplying # of rows particular subset of b.