Slow Mysql Query / In Clause

I have a database with ~100,000 entries - right now my query looks like this:

SELECT id,zipcode,price FROM listings WHERE zipcode IN (96815 , 96815, 96835, 96828, 96830, 96826, 96836, 96844, 96816, 96847, 96814, 96822, 96823, 96843, 96805, 96806, 96810, 96848, 96808, 96809, 96842, 96839, 96802, 96812, 96804, 96803, 96840, 96807, 96813, 96841, 96801, 96850, 96811, 96开发者_C百科898, 96837, 96827, 96824, 96846, 96821, 96817, 96859, 96838, 96819, 96820, 96858, 96849, 96825, 96795, 96863, 96818, 96853, 96861, 96734, 96744, 96701, 96860, 96709, 96782, 96706, 96797, 96862, 96789, 96707, 96730, 96854, 96759, 96786, 96857, 96717, 96792, 96762, 96712, 96791, 96731) AND (price BETWEEN 1000 AND 1200) ORDER BY id

This query worked fine when I had only 50,000 entries, but its getting really slow when I extend the radius of the zip codes (which results in even more zip codes in the in clause).

id int(8) AUTO_INCREMENT price mediumint(7) zipcode mediumint(5)

All three fields are indexed. Does anyone have an idea how i could optimize it? Thanks guys

ZIP codes are generally geographically distributed -- you might be able to speed things up by simplifying the ZIP code clause to something like:

zipcode BETWEEN 96700 AND 96898

This may end up inadvertently including some zip codes that you didn't want, but it may end up being more efficient to filter them out later.

There is a big difference between each field being indexed and fields being indexed correctly. As far as I can see you only need one index to make your query run faster

Try create an index with the following columns (zipcode, price)