Optimizing this query

Question

I'm trying to optimize the following query but I don't know what to do. I know little of query optimization but doesn't that key_len mean its searching through 386,386 rows? The cities table has 10,053 rows and states only 27.

SELECT c.city_name, s.state_name
FROM res_geo_cities AS c, res_geo_states AS s
WHERE c.id_state = s.id_state AND(
(
 (c.metaphone_primary IN ($one,$two) OR c.metaphone_secondary IN ($one,$two)) AND 
 (s.metaphone_primary IN ($three,$four) OR s.metaphone_secondary IN ($three,$four))
) OR (
 (c.metaphone_primary IN ($three,$four) OR c.metaphone_secondary IN ($three,$four)) AND 
 (s.metaphone_primary IN ($one,$two) OR s.metaphone_secondary IN ($one,$two))
))
LIMIT 50

This is the explain:

alt text

Could somebody be so kind as to point me in the right direction?

mylesmg · Accepted Answer · 2010-12-07 23:59:14Z

1

This means that you have two keys that each have a length of 386. Try rewriting this query with a join that has an 'ON' clause, instead of just selecting from multiple tables. Hope this helps.

answered Dec 7, 2010 at 23:59

mylesmg

5323 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Optimizing this query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related