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...
I found a solution. it does 50000 rows in 0.300 sec.