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?