Why is this INNER JOIN/ORDER BY mysql query so slow?

Question

I have very big database of customers. This query was ok before I added ORDER BY. How can I optimize my query speed?

$sql = "SELECT * FROM customers 
LEFT JOIN ids ON customer_ids.customer_id = customers.customer_id AND ids.type = '10'
ORDER BY customers.name LIMIT 10";

ids.type and customers.name are my indexes

Explain query

id  select_type  table      type    possible_keys   key     key_len     ref     rows    Extra   
1   SIMPLE       customers  ALL     NULL            NULL    NULL        NULL    955     Using temporary; Using filesort
1   SIMPLE       ids        ALL     type            NULL    NULL        NULL    3551    Using where; Using join buffer (Block Nested Loop)

If you can add good indexes to other fields, do so. This makes sense btw because with order by, it needs the entire results set first before it can rearrange it. Also, what are your primary keys? — user4413591
Your query is syntactically incorrect. customer_ids is not defined. — Gordon Linoff
He is referring to the fact that customer_ids is used in his join criteria but is in fact not one of the tables that was joined on. Can you show the results of an EXPLAIN on the above query? — BK435
@Isaiah: because the from clause does not contain such a table: FROM customers LEFT JOIN ids ON ... so the query contains a reference to the table customers and the table ids. — a_horse_with_no_name
Sorry, i wrote this query as a example and that's why there is wrong table name. It should be LEFT JOIN ids ON ids.customer_id... — lingo

Andras Andras · Accepted Answer · 2015-02-08T06:36:07

(I assume you meant to type ids.customer_id = customer.customer_id and not customer_ids.customer_id)

Without the ORDER BY mysql grabbed the first 10 ids of type 10 (indexed), looked up the customer for them, and was done. (Note that the LEFT JOIN here is really an INNER JOIN because the join conditions will only hold for rows that have a match in both tables)

With the ORDER BY mysql is probably retrieving all type=10 customers then sorting them by first name to find the first 10.

You could speed this up by either denormalizing the customers table (copy the type into the customer record) or creating a mapping table to hold the customer_id, name, type tuples. In either case, add an index on (type, name). If using the mapping table, use it to do a 3-way join with customers and ids.

If type=10 is reasonably common, you could also force the query to walk the customers table by name and check the type for each with STRAIGHT JOIN. It won't be as fast as a compound index, but it will be faster than pulling up all matches.

And as suggested above, run an EXPLAIN on your query to see the query plan that mysql is using.

Why is this INNER JOIN/ORDER BY mysql query so slow?

2 Answers