Optimizied index for a query

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

have just one compound index, on lat and long

:slight_smile:

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?

Maybe a better way to ask it, do you know a good reference source on how MySQL treats and uses indexes?

One more question, in thinking about this it makes perfect sense. So if do a query

 select name from t1 where name like "hou%" and lat > x and lat < y and lng > a and lng < b 

should I have an indes on name/lat/lng or would it make difference to have it on lat/lng/name?

do you know how to do an EXPLAIN, and how to read the results?

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?

Thanks

in order to help you with the explain, we would need three things from you –

  • result of SHOW CREATE TABLE for each of the tables in the query

  • the actual query itself

  • the EXPLAIN in its normal columnar format