Hi all,
I have a performance issue with the following query:
SELECT DISTINCT name FROM customproperty ORDER BY name;
The table and an index is defined by:
CREATE TABLE customproperty (parentID INTEGER NOT NULL,
name VARCHAR(100) NOT
NULL,
val VARCHAR(100))
CREATE INDEX ixCpN ON customproperty (name)
The pair(parentID, name) is unique, but name is not.
The table customproperty does get quite large, the number of distinct names
is much smaller.
>From reading the VDBE code and running sqlite through a profiler, I figured
the select distinct query is executed this way:
- read every row in the table. (*1)
- every row is checked if it contains a new name.
Hence, I end up with R read operations and R * log D comparison operations.
(R is the number of rows in customproperty,
D is the number of distinct names in customproperty)
So, it seems sqlite 3.2.5 does not use the index to speedup DISTINCT
queries.
My question to you:
How can SQL statements be reformulated to improve the select distinct
performance?
For example through better usage of the information in the index?
Thanks
Ralf
Remark:
(*1) Actually, the cursor iterates over the index if 'ORDER BY name'
