Optimize LEFT JOINS ORDER BY on multiple varchar columns

Question

Hello everyone,

I tried to optimize a MySQL query which sort multiple varchar columns:

SELECT *
FROM tickets
LEFT OUTER JOIN customers ON customers.id = tickets.customer_id
LEFT OUTER JOIN locations ON locations.id = tickets.location_id
ORDER BY customers.name, locations.name;

The ORDER BY statement seems to cost lot of time(~100ms) for a small database of ~6000 tickets, ~40 customers and ~400 locations.

I already reduced the length of varchar columns, which had significantly speed up the query(2x faster).

Do you have any solutions to optimize the query execution time?

Many thanks!

Answer 1

You should have indexes on the customers.name and locations.name columns.

Additionally, ensure that customers.id, tickets.customer_id, locations.id, and tickets.location_id are all indexed.