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; ---------------------------------------------------------- SQLite version 3.2.1 Enter ".help" for instructions sqlite> .schema CREATE TABLE favorite (id text not null default '' primary key, url text not null default '' unique, title text not null default '', desc text not null default '', itime datetime not null default '0000-00-00 00:00:00', classpublic int(10) not null default 0, classprivate int(10) not null default 0, rank int(10) not null default 0, ispub int(10) not null default 0, auditflag int(10) not null default 0, pv int(10) not null default 0, collect int(10) not null default 0, f1 text not null default '', f2 text not null default '', f3 text not null default '', f4 text not null default '', f5 text not null default ''); CREATE TABLE t1 (id integer not null default 0 primary key, f1 text not null default ''); CREATE INDEX auditflag on favorite (auditflag); CREATE INDEX classprivate on favorite (classprivate); CREATE INDEX classpublic on favorite (classpublic); CREATE INDEX collect on favorite (collect); CREATE INDEX f1 on t1 (f1); CREATE INDEX f2 on t1 (f2); CREATE INDEX ispub on favorite (ispub); CREATE INDEX itime on favorite (itime); CREATE INDEX pv on favorite (pv); CREATE INDEX rank on favorite (rank); regards gary 2005-4-29
