SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Evangelist
    Join Date
    Nov 2009
    Posts
    471
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    have just one compound index, on lat and long

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    Nov 2009
    Posts
    471
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  4. #4
    SitePoint Evangelist
    Join Date
    Nov 2009
    Posts
    471
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Maybe a better way to ask it, do you know a good reference source on how MySQL treats and uses indexes?

  5. #5
    SitePoint Evangelist
    Join Date
    Nov 2009
    Posts
    471
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    One more question, in thinking about this it makes perfect sense. So if do a query
    Code:
     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?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    do you know how to do an EXPLAIN, and how to read the results?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist
    Join Date
    Nov 2009
    Posts
    471
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  8. #8
    SitePoint Evangelist
    Join Date
    Nov 2009
    Posts
    471
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •