I have a query that is run many times always based on a lat/lng query first where it is lat > x and lat < x and lng > y and lng < y. There may be additional criteria such as group by name or such but the lat/lng is always there.
What is the best indexing scheme to accomodate this? Right now I have 2 separate indexes on lat and lng.
Thanks, regarding the individual indexes on lat and lng, should I leave those or remove them? Is there a price for too many indexes? Or in this case, if I had one on lat/lng pair, would it use the individuals at all?
I just ran across that in some research and was going to try it. I think I can figure it out from here. If there are more questions I’ll be back but I really appreciate the help.
I have run EXPLAIN and have a question. I have a table with name city and state. I have indexes on city, state and city_state and state_city (just for imformative purposes, would never leave them all). If I run an EXPLAIN select name from t1 where city = ‘x’ and state = ‘y’; EXPLAIN Extra says “Using where”; key says city_st; possible_keys says state, city, city_st, st_city; ref says “const, const”; select_type says SIMPLE; type = “ref”.
My question is why wouldn’t it be an “index” type and why wouldn’t it say “Using index” in the Extra column?