caokai wrote:
hi,guys:
I do think sql query speed may not change by just change the join order of 2 tables.
But I meet this problem in sqlite just now. so I think something must be wrong in "group by" process in sqlite.
In this case: favorite table have more than 1000 records,and t1 table have less than 20 records change the table order will have dramatic influence on the query performance .
below sql would takes about 5 seconds (slow)sqlite> select count(1),t.f2 from t1 t inner join favorite f on t.f1=f.id where f.ispub=1 group by t.f2;
but if I change the order of 2 table as below,it takes less than 1 second
(fast)sqlite> select count(1),t.f2 from favorite f inner join t1 t on t.f1=f.id where f.ispub=1 group by t.f2;
You can read the slides that Richard wrote for the php2004 conference. http://www.sqlite.org/php2004/page-001.html Pages regarding your question are 56 and sequent.
Paolo
