CODEWITHSUNDEEP
mysql
user2694306
user2694306

Reputation: 4050

Proper Way to Create Compound Index on MySQL

I am trying to improve the results on an expensive mysql search. I Have a query in Python that looks like the following:

query = ("SELECT s.* "
         "FROM stores_standardized ss "
         "LEFT JOIN stores s "
         "ON s.storeID = ss.store_ID "
         "WHERE s.phone = %s "
         "OR (ss.fulladdress = %s "
         "AND ss.city = %s "
         "AND ss.state = %s "
         "AND ss.zip = %s "
         "AND ss.country = %s "
         "AND ss.number = %s "
         "AND ss.street = %s "
         "AND ss.type = %s) "              
         "AND ss.standardizedname = %s"
     )

I know that I need an index, but I'm confused as to where I should start my index. Which of the following (or something else), would be the best index?

Option 1:

create index idx 
on stores_standardized
(fulladdress,city,state,zip,country,number,street,type,standardizedname);

and then have a separate index for store_ID on stores_standardized. Or is it better to have them all combined:

Option 2:

create index idx 
on stores_standardized
(store_ID,fulladdress,city,state,zip,country,number,street,type,standardizedname);

Edit Running explain provides the following:

+------+-------------+-------+--------+---------------------+---------+---------+----------------------------+---------+-------------+
| id   | select_type | table | type   | possible_keys       | key     | key_len | ref                        | rows    | Extra       |
+------+-------------+-------+--------+---------------------+---------+---------+----------------------------+---------+-------------+
|    1 | SIMPLE      | ss    | index  | name,search,search2 | search2 | 864     | NULL                       | 1803772 | Using index |
|    1 | SIMPLE      | s     | eq_ref | PRIMARY             | PRIMARY | 8       | dbname.ss.store_ID         |       1 | Using where |
+------+-------------+-------+--------+---------------------+---------+---------+----------------------------+---------+-------------+

Upvotes: 0

Views: 50

Answers (1)

Uueerdo
Uueerdo

Reputation: 15961

In this particular case, I believe the only indexes that can help this query are on the store_ID field of each table; the OR in your WHERE conditions will pretty much eliminate the use of indices there; also, your X OR (Y) AND Z condition is slightly ambiguous to look at.

Your best bet for performance increases is to separate it out into separate similar queries (each with one part of the OR) and UNION those queries together. The UNION will eliminate any duplicate results, and you can then take advantage separate indices for each part; one with an index on phone and another on (some of) the list of field with ANDed conditions; which fields would be best will vary based on your expected data distribution.

Upvotes: 1

Related Questions