MySQL query does not use index

Question

The following query:

EXPLAIN 
SELECT 
    fdb . * , 
    TIME_FORMAT( fdb.scheme,  '%H:%i' ) AS scheme, 
    TIME_FORMAT( fdb.actual,  '%H:%i' ) AS actual, 
    TIME_FORMAT( fdb.baggage, '%H:%i' ) AS baggage, 
    TIME_FORMAT( fdb.baggage_handled,  '%H:%i' ) AS baggage_handled, 
    ff . * , TIME_FORMAT( ff.actual_saved,  '%H:%i' ) AS actual_saved, 
    TIME_FORMAT( ff.baggage_saved,  '%H:%i' ) AS baggage_saved, 
    TIME_FORMAT( ff.baggage_handled_saved,  '%H:%i' ) AS baggage_handled_saved, 
    ap.device_id, 
    ap.device_token, 
    ap.device_language, 
    ap.app_edition, 
    ap.receive_status_notifications, 
    ap.receive_time_notifications, 
    ap.receive_luggage_notifications, 
    ap.receive_gate_notifications, 
    ap.receive_runway_notifications, 
    ap.receive_plane_notifications
FROM flights_database fdb
    JOIN flights_followed ff ON fdb.flight_id = ff.flight_id
    JOIN apns_users ap ON ff.device_id = ap.device_id
                        AND ap.app_edition =  '1'

After using Explain, it is clear the query uses table scans: enter image description here

There are various keys and pusher_idx contains both device_id and flight_id. Why is this index not used?

I think the index can only be used as an index when you ONLY refer to indexed fields. If you join in non-indexed fields from the same table you get a scan. Also expect that function calls will break index into scan. — Pieter21, Sep 25 '14 at 22:03
`SHOW CREATE TABLE \`apns_users\`;`, `SHOW CREATE TABLE \`flights_followed\`;` — Sebas, Sep 25 '14 at 22:22

score 0 · Accepted Answer · answered Sep 25 '14 at 22:25

0

In this case mysql optimizer changed the order of your tables (it can do it - they are all INNER JOINS):

SELECT 
    ...
FROM flights_followed ff
    JOIN flights_database fdb ON fdb.flight_id = ff.flight_id
    JOIN apns_users ap ON ff.device_id = ap.device_id
                        AND ap.app_edition =  '1'

So as soon as flights_followed is not filtered by any own predicate (with new join order it's other tables joined to it, so it's they that are filtered) - it decides to perform a full scan.

answered Sep 25 '14 at 22:25

zerkms

249,484
69
436
539

@edwardmp: to solve "what"? You requested a full scan - mysql performed it. If you don't want a full scan - add some predicate for `ff` to `WHERE` (or relevant `ON`) – zerkms Sep 26 '14 at 01:07
I misunderstood. Thanks. Adding a WHERE on ff still uses table scan though :( – edwardmp Sep 26 '14 at 15:31
@edwardmp: well, I did not say that **ANY** predicate in `WHERE` will prevent it from full scan. You asked a question, I answered it. If you need help with another query - put it into the question or ask another question. – zerkms Sep 26 '14 at 20:49

MySQL query does not use index

1 Answers1