Brownie <[EMAIL PROTECTED]> wrote:
I have a table and indices as follows;
CREATE TABLE employee(name TEXT,salary INTEGER,job TEXT);
CREATE INDEX idx_emp_salary_job ON employee(salary,job);
CREATE INDEX idx_emp_job ON employee(job);
When I use the following query, SQLite seems to use idx_emp_salary_job
for both WHERE and GROUP BY.
SELECT job, COUNT(name) FROM employee WHERE salary=100 GROUP BY job;
0|0|TABLE employee WITH INDEX emp_salary_job ORDER BY
But when modify this query as follows, SQLite seems to use
idx_emp_salary_job for WHERE only. Not used for GROUP BY.
SELECT job, COUNT(name) FROM employee WHERE salary>=100 GROUP BY job;
0|0|TABLE employee WITH INDEX emp_salary_job
Because index on (salary, jobs) is not helpful here. This index lists
rows ordered by salary, and for each distinct value of salary rows are
ordered by job. Your first query narrowed down to a single value of
salary, at which point the index gave you rows ordered by job and group
by was trivial. But now that you have multiple salary values, SQLite has
to sort on job anyway.
See if this query gets better performance:
SELECT job, COUNT(name) FROM employee WHERE +salary>=100 GROUP BY job;
Unary plus would suppress the index involving salary, so one on (job)
should be used.
Another thing to try is to create an index on (job, salary) rather than
(salary, job).
So, I modify it by using WHERE EXISTS and subqueries.
SELECT job, COUNT(name) FROM employee WHERE EXISTS
(SELECT * FROM employee WHERE salary>=100) GROUP BY job;
This statement doesn't make any sense. The subselect doesn't depend on
the outer select in any way, so it would be executed once and EXISTS
clause will either produce 1 or 0. Assuming there are indeed records
with salary >= 100, this query is equivalent to
SELECT job, COUNT(name) FROM employee GROUP BY job;
which is quite different from the original query.
0|0|TABLE employee WITH INDEX emp_job ORDER BY
0|0|TABLE employee WITH INDEX emp_salary_job
It seems to use indices for both WHERE EXISTS and GROUP BY.
Why not? They are separate, largely unrelated queries.
Igor Tandetnik
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
