SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Order tables are searched

    Hi guys,

    We have two platforms; one is for dev and uses InnoDB and one is production and uses NDBCluster. If I EXPLAIN a query on the dev server all is well: 259 rows found in the first table, and all other joins return a single result. Perfect. If I do the same on the prod server, I get 80 rows on the first table, then a few 1s and then 18,356, then a few more 1s.

    So, looking a bit further in to it, the tables are in a different order in each EXPLAIN, and the one that returns 18,356 isn't using an index, which it is on dev. Another table is now using two columns as references, whereas on dev it's only one.

    Looking at the order in which the tables are returned in the EXPLAIN, the table that is returning 18,356 rows should be the second in a group of three, as it's a lookup table, but it's coming in first.

    What on earth is going on? How do I make it process the tables in the correct order? Is it likely that it's a problem with the indexes?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Antnee View Post
    Is it likely that it's a problem with the indexes?
    yes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I thought so, thanks


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
  •