2

I have the following:

SELECT * FROM autorepair
WHERE zip_code IN (91316,91356,91416,91426,91436)
AND specialty = 'transmission'
ORDER BY ABS(91316 - `zip_code`)
LIMIT 0,10

Indexes:

-zip_code -specialty

How can I improve the performance on queries like these?

BTW, I'm running this on XAMPP.

  • 1
    I can't help wondering why you're using a zip code in an arithmetic expression? This image springs to mind :-) – Vérace Aug 22 '14 at 11:36
3

Create a compound index as follows:

ALTER TABLE autorepair ADD INDEX specialty_zipcode_ndx (specialty,zipcode);

and drop the single column specialty index

ALTER TABLE autorepair DROP INDEX specialty;
  • 1
    Great, thanks! from 0.5s to 0.09s. Under what general circumstances should I use compound indexes? Is it always better to use compound indexes vs having 2 separate indexes? – user3728429 Jun 11 '14 at 4:27
  • If you execute EXPLAIN plus your query, you will get information about index utilization, then you can see when a compdound index is better. I thought that you had it already and my answer was in the ORDER BY optimization way. – corretge Jul 10 '14 at 14:04
  • I know this is like 3 years old, but I'm trying to understand something. Was it necessary to drop the 'speciality' index in order to get the speed advantages from the new compound index? – HK1 Jun 26 '17 at 16:26
0

Yes, you can:

remove the calculations in the ORDER BY clausule.

If you EXPLAIN your query, you can see that it's using filesort instead your index.

try with

 ORDER BY `zip_code` DESC
  • Downvote without explanation? I propose that because 91316,91356,91416,91426,91436 are greater or equals to 91316. And the calculations in ORDER BY are penalized. – corretge Aug 22 '14 at 10:25
  • Ok, I forget the DESC in the ORDER BY, mea culpa – corretge Aug 22 '14 at 10:26

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.