Not saying this will be faster for sure, but it's worth a try. Try a
composite index on (dept, salary). The goal is to rank distinct salaries
within department, and have those salary rankings be used as the inner
loop when the EEs table is joined to the ranking on the salary column.
The assumption is that there are far fewer distinct salaries than
distinct employees.
Tim Romano
CREATE TABLE "EES" ("id" int PRIMARY KEY NOT NULL , "dept" int NOT NULL
, "salary" int NOT NULL , "empname" text NOT NULL )
select EES.dept, EES.empname, S3.rank from EES
JOIN
(
select s1.dept, s1.salary,
(select count(*) + 1 from
(select distinct dept, salary from EES) as S2 where S1.dept=s2.dept and
s1.salary < s2.salary) as rank
from
(select distinct dept, salary from EES) as S1
) as S3
ON S3.dept = EES.dept and S3.salary = EES.salary
order by EES.dept, rank
Explain Query Plan:
Order, from, detail
0,0, TABLE EES WITH INDEX EES_IX ORDER BY
0,1, TABLE AS S1
1,0, TABLE EES WITH INDEX EES_IX
0,0, TABLE EES WITH INDEX EES_IX ORDER BY
0,0,TABLE AS S2
0,0, TABLE EES WITH INDEX EES_IX ORDER BY
0,0, TABLE AS S2
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
