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 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; 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. Is there more efficient way for the query that have inequalities and GROUP BY? Regards, ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
