bostboy — 2011-03-22T12:32:32-04:00 — #1
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.
r937 — 2011-03-22T14:55:25-04:00 — #2
have just one compound index, on lat and long
bostboy — 2011-03-22T15:04:15-04:00 — #3
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?
bostboy — 2011-03-22T15:04:52-04:00 — #4
Maybe a better way to ask it, do you know a good reference source on how MySQL treats and uses indexes?
bostboy — 2011-03-22T15:10:40-04:00 — #5
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?
r937 — 2011-03-22T16:12:41-04:00 — #6
do you know how to do an EXPLAIN, and how to read the results?
bostboy — 2011-03-22T17:19:53-04:00 — #7
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.
bostboy — 2011-03-22T19:01:49-04:00 — #8
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?
r937 — 2011-03-22T20:01:08-04:00 — #9
in order to help you with the explain, we would need three things from you --