Mysql – Would adding indexes to the foreign keys improve performance on this MySQL query

indexindex-tuningMySQLperformancequery-performance

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!

Best Answer

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

SUGGESTION #1 : Use index with different column order

ALTER TABLE sales_reps ADD UNIQUE INDEX
(state,account_id,name,platform,client_group,from_date);

Note that state and account_id are reversed. That way, there is no need to traverse all rows for a given account_id that would separate enabled from disabled. Only enabled entries for account_id are read.

SUGGESTION #2 : Refactor the Query

Instead of

SELECT * 
FROM `sales_reps` 
WHERE `sales_reps`.`account_id` = 1 
AND state = 'enabled' 
AND (clients >= 1 OR revenue >= 100) 
ORDER BY name,platform,client_group,from_date

Try this experiment

First run this query

SELECT enabled,accountid,name,platform,client_group,from_date
FROM sales_rep WHERE account_id=1 AND state='enabled'
ORDER BY enabled,accountid,name,platform,client_group,from_date

then run this query

SELECT * FROM sales_rep WHERE account_id=1 AND state='enabled'
AND clients >= 1 OR revenue >= 100

If the performance of those two queries are fast, then JOIN them as subqueries and see

SELECT B.* FROM
(
    SELECT enabled,accountid,name,platform,client_group,from_date
    FROM sales_rep WHERE account_id=1 AND state='enabled'
    ORDER BY enabled,accountid,name,platform,client_group,from_date
) A LEFT JOIN
(
    SELECT * FROM sales_rep WHERE account_id=1 AND state='enabled'
    AND clients >= 1 OR revenue >= 100
) B USING (enabled,accountid,name,platform,client_group,from_date)
WHERE B.enabled IS NOT NULL;

Give it a Try !!!

Best Answer

Related Solutions

Mysql – High Disk IO, How to mitigate

Mysql – What would be a optimal index for this query in thesql

SUGGESTION #1 : Use index with different column order

SUGGESTION #2 : Refactor the Query

Related Question