MySQL subquery 次序与速度

小弟发觉相同的 subquery 但次序不同, 速度可以差好远, 例如:

SQL1 fast:
select x, sum(y) from table1 where A and B and x in (select x from table2 where C order by x) group by x order by x;

SQL2 slow:
select x, sum(y) from table1 where x in (select x from table2 where C order by x) and A and B group by x order by x;

请问有冇朋友知点解? 唔该晒.

Subquery内不需 "order by" 啊.

原因应该系三重筛选过程中, 最好先行成本低...
A
B
x in (select x from table2 where C order by x)

Subquery拉两个table, 每一笔难度比 check手头table高很多.
如果每一关都系十选一, check A,B每笔成本系 t, check subquery每笔成本系 10t.
假如有10000record, 三重筛选得10records...
SQL1 : 10000t + 1000t + 100x10t = 1200 t
SQL2 : 10000x10t + 1000t + 100t = 101100 t


Remarks:
计算不严谨, 大概俾个sense你.
其实我相信都会data sensitive, 可以一砌到data set出来速度调转, 不过冷门些.
每笔成本, 另外要考虑筛选比率回收越少越好 (筛得多), 可得出相反结论.
- 如果subquery系万选一, A,B系十选十.
- SQL1 : 10000t + 10000t + 10000x10t = 120000 t
- SQL2 : 10000x10t + 1t + 1t = 100020 t

要俾 cost based optimizer 做啱嘢就要有正确的统计资料.
See http://dev.mysql.com/doc/refman/5.1/en/optimizer-issues.html