The change seems to have happened within the 19 release. I am currently on build 43 and this is the first time this happens. When I upgraded a major release in the past (maybe 2 years ago), I had to change all my where clauses because it did not pick the same order anymore. I don't think it has to do with indexes, because MI seems to build indexes on the fly when it realizes it would gain from it. It also does not seems to be due to the use of functions. It is more an issue of the following nature, let's imagine the following queries (A1, A2, B1 and B2 are all indexed):
select A.A1, A.A2, B.B2 from A, B where A.A1 = B.B1 into C
select A1, B2 from C where A2 = B2 into E1
select A.A1, B.B1, B.B2 from A, B where A.A2 = B.B2 into D
select A1, B2 from D where A1 = B1 into E2
select A.A1, B.B2 from A, B where A.A1 = B.B1 and A.A2 = B.B2 into E3
select A.A1, B.B2 from A, B where A.A2 = B.B2 and A.A1 = B.B1 into E4
Presumably, building E3 is equivalent of building E1 and building E4 is equivalent to building E2, unless it is the other way around. Let's say that A has 3,000,000 records and B has 1,000,000 records, and E1, E2, E3, E4 only have 12,000 records (they are identical). Let's now say that C has 378,000,000 records and D has 53,000 records. Building E1 is 1000 times costlier than building E2. My current code generates E4, which used to be equivalent to generating E2 using the previous MI release. But now, it seems like it is equivalent to building E1. I will need to modify hundreds of queries in MapBasic, but I need to understand exactly how this works. I hope I get the attention of people who have access to that information.