Would adding indexes to my foreign keys improve performance on this MySQL query?

Question

Consider the following query:

SELECT
  `locations`.`id` AS `location_id`,
  `locations`.`address`,
  `locations`.`lat`,
  `locations`.`lng`,
  `tickets`.`status_id`,
  `customers`.`name`,
  `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'
AND
  (
    `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!

Answer 1

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).

Finally,

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'
  AND
  (
    `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.