Consider the following query:

  `locations`.`id` AS `location_id`,
  `tickets`.`id` AS `id`,
  `tickets`.`updated_at` AS `updated_at`,
  ( 3959 * acos( cos( radians('39.78222851322262') ) * cos( radians( `lat` ) ) * cos( radians( `lng` ) - radians('-86.16299560000004') ) + sin( radians('39.78222851322262') ) * sin( radians( `lat` ) ) ) ) AS `distance`
FROM `locations`
RIGHT JOIN `tickets`
  ON (`tickets`.`location_id` = `locations`.`id`)
LEFT JOIN `customers`
  ON (`tickets`.`customer_id` = `customers`.`id`)
WHERE `tickets`.`client_id` = '20'
    `customers`.`name` LIKE '%Mahoney%'
    OR `customers`.`email` LIKE '%Mahoney%'
    OR `locations`.`address` LIKE '%Mahoney%'
HAVING `distance` < '5'
ORDER BY `distance`
LIMIT 200;

Using a profiling tool, I got this report:

Speed: 45.569 ms
Query analysis:
· Query: SIMPLE on tickets · Type: ALL · Rows: 160 (Using where; Using temporary; Using filesort)
· Query: SIMPLE on locations · Possible keys: PRIMARY · Key Used: PRIMARY · Type: eq_ref · Rows: 1
· Query: SIMPLE on customers · Possible keys: PRIMARY · Key Used: PRIMARY · Type: eq_ref · Rows: 1 (Using where)

This is a MySQL database. All the tables are InnoDB with utf8_unicode_ci. The primary keys on each table are called id and are int(11) with indexes on them.

  • Should adding an index on tickets.location_id and/or tickets.customer_id and/or tickets.client_id improve performance of this query at all?
  • Why or why not?
  • Are there any other fields I should consider indexing to improve the efficiency of this query?
  • Should I be explicitly defining my foreign keys in my database schema?

My thinking is that since I'm selecting from locations first, I would want an index on the foreign keys that are being referenced. I read here: indexes, foreign keys and optimization that MySQL requires indexes on all foreign keys. Does explicitly defining the foreign keys in an InnoDB schema improve performance? Sorry for being such a total n00b. Thanks for the help!

  • 1
    Partial response: Create a composite index on the lat and lng of the locations table and then search a 'box' around your search radius. Simply find the highest and lowest possible values for lat and lng each and add locations.lat <= :max_lat, etc, to your query. With that, you will greatly narrow the number of records looked at as determined by location. Jul 9 '13 at 19:42
  1. Yes, performance may be better if you add those indexes. However, with such a small number of rows, it's quite possible that full table scan is more efficient and optimizer choses not to use any indexes.
  2. After adding indexes your execution plan will be different, to get a rough estimation of how effective the indexes are you can multiply "Rows" column for each line of output of explain
  3. In general, indexes on fields which participate in filtering/join conditions/order/group by improve performance. You also need to take into account selectivity (how many distinct values you have) of the column; if it's too low , the engine will not use it except if it's covering index for a query.
  4. Foreign key is a constraint; the main purpose of any constraint to enforce some restriction (referential integrity in case of FK). Thus, if you care about integrity of you data, you should add foreign constraint.

The fact that Mysql implicitly creates an index on FK column means better read performance, and bit worse insert/update/delete performance (because index itself has to be updated).


My thinking is that since I'm selecting from locations first, ....

is not absolutely correct. Physical processing is not the same as logical; optimizer decides in which order it will process tables involved (as you can see in your output, the engine first accesses tickets table) and what access method to use. You can control it to some extent with hints though...

*Side note. The way your WHERE clause written :

WHERE `tickets`.`client_id` = '20'
    `customers`.`name` LIKE '%Mahoney%'
    OR `customers`.`email` LIKE '%Mahoney%'
    OR `locations`.`address` LIKE '%Mahoney%'

makes your LEFT JOIN customers behave as INNER JOIN.*

Update Never mind my side note, I didn't pay attention you have ORs with multiple tables.

I hope it was helpful.

  • Thanks @a1ex07. The profile was run on our dev DB. The production DB has 10s of thousands of records, and will grow to hundreds of thousands if not millions.
    – Ben Harold
    Jul 9 '13 at 20:45
  • 2
    You definitely need to get the query plan from the production server, because population of the tables and cardinality of the indexes can dramatically change the query plan. Jul 9 '13 at 20:46
  • Point 1 would be correct, I suspect, only if the outer joins were actually written as inner joins. As you've pointed out, the where clause makes these effectively inner joins (locations is the same situation, notice that icky right join), but the optimizer may not be able to work that out. Until the query is rewritten, indexes on tickets.location_id and tickets.customer_id can't help because the query is essentially tickets left join locations left join customers and the query plan seems to confirm this. Rewrite the joins and then absolutely those indexes might help. Jul 9 '13 at 20:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.