0
SELECT `Nen Straatnaam` as street, `Nen Woonplaats` as city, Gemeente,
   Postcode, acn_distinct.zipcodes, acn_distinct.lat, acn_distinct.lng 
FROM  `acn_distinct` INNER JOIN crimes as c
   ON `Nen Woonplaats` = c.place AND c.street_check = 0 
ORDER BY street ASC

EXPLAIN gives me this information:

id  select_type table           type    possible_keys                               key                 key_len ref             rows    Extra
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 is it not using the suggested indexes?

Bernhard Barker
  • 54,589
  • 14
  • 104
  • 138
Buar
  • 11
  • 7
  • 1
    one question i might be wrong but join condition seems like a where clause; acn_distinct table join crimes table on a specific column? – Anda Iancu Aug 15 '13 at 14:57
  • It is using indexes; the `key` column in the explain info gives you the index mysql is using. – Colonel Thirty Two Aug 15 '13 at 14:59
  • @Colonel Thirty Two shouldn't it be writen using indexes, or Using index condition on the first row too then ? – Buar Aug 15 '13 at 15:05
  • @Gordon Linoff: there IS a reference to the second table. `Nen Woonplants` is a column in the second table. It's not qualified, so we don't know from sure from just the SQL text (this is an example where the best practice of qualifying every column reference with a table alias would help the reader.) The EXPLAIN output shows an index named `ID_nen_woonplaats` being used to access that second table, with a reference of c.place, that confirms this is a column in that table. – spencer7593 Aug 15 '13 at 15:50
  • @Buar `Using index` means that a [covering index](http://stackoverflow.com/questions/62137/what-is-a-covered-index) is used. `ID_nen_woonplaats` here can be used as an index (which is why it is in the `key` column) but it can't be used as a covering index, which is why `Using index` does not appear. – Colonel Thirty Two Aug 15 '13 at 21:46

1 Answers1

1

It is using an index, idx_street_check, however, the performance will be terrible, since it's also creating a temporary table and using filesort which are both notorious culprits.

The question is why it's not using an index on crimes.place. I would try creating a multi-column index on (place, street_check).

But more importantly, I think your table schema is very, very bad. Your JOIN is poorly formed:

FROM  `acn_distinct` INNER JOIN crimes as c
   ON `Nen Woonplaats` = c.place AND c.street_check = 0

When you do a JOIN between tables A and B, you should join as such: A.x = B.y. But in this case you're not even referring to table A. You're multiplying the # of rows from A by a particular subset of B.

ktm5124
  • 11,861
  • 21
  • 74
  • 119
  • Thnx for answer will try add (place, street_check) index. So how should i format this query ? SImply add `acn_distinct`.`Nen Woonplaats` = c.place? – Buar Aug 15 '13 at 15:20
  • You join two tables on a column from each respective table. Is `Nen Woonplaats` a column in your table `acn_distinct`? Please add the output of `describe acn_distinct` to your post. – ktm5124 Aug 15 '13 at 15:31
  • @Ktm5124: yes, that's a column in `acn_distinct`. The EXPLAIN output confirms it; index name `ID_nen_woonplaats` with ref from `c.place`. This example illustrates why qualifying EVERY column reference with a table alias is best practice. – spencer7593 Aug 15 '13 at 15:53