How to optimize ORDER BY in mysql?

Question

I have this query in mysql:

SELECT DISTINCT phone.phone AS phone
FROM phone 
 INNER JOIN person ON person.id = phone.person_id
 INNER JOIN locality  ON locality.id = person.locality_id
 INNER JOIN city ON city.id = locality.city_id
 INNER JOIN department ON department.id = city.department_id
 ORDER BY phone.id
LIMIT 10

EXPLAIN

TYPE      TABLE     TYPE    POSIBLE_KEYS                                 KEY               LEN     REF         ROWS     Extra
SIMPLE  department  index   PRIMARY                                       PRIMARY           4                   19      Using index; Using temporary; Using filesort
SIMPLE  city        ref     PRIMARY,fk_city_department                 fk_city_department   4   department.id   6       Using index
SIMPLE  locality    ref     PRIMARY,fk_locality_city                    fk_locality_city    4   city.id         1       Using index
SIMPLE  person      ref     PRIMARY,fk_person_locality                 fk_person_locality   5   locality.id     1596    Using index
SIMPLE  phone       ref     idx_phone,idx_phone_person,fk_phone_person  fk_phone_person     9   person.id       1   

Table phone has 900000 rows, Table person has 700000 rows and Tables department, city and locality have 250 rows each.

The excecution time with "ORDER BY phone.id" is 20.125 sec , and without "ORDER BY phone.id" is 0.001 sec.

I need ORDER BY, How can I optimize the query?

Thanks a lot...

Answer 1

I found a solution. it does 50000 rows in 0.300 sec.

     SELECT * FROM (SELECT phone.phone, phone.id AS pid FROM phone
        INNER JOIN person ON person.id = phone.person_id
        INNER JOIN locality ON locality.id = person.locality_id
        INNER JOIN city ON city.id = locality.city_id
        INNER JOIN department ON department.id = city.department_id 
        LIMIT 50000) AS tableaux
         ORDER BY tableaux.pid

Answer 2

Is there separate index on order by field? Add it if not