Please ignore my previous mail, it was accidentally sent.
To continue with my previous mail,
Offlate I have observed a strange behavior with Query Optimizer. I am not
sure if it is the desired behavior, yet please find my observations below:
Table
employee_table
-----------------------------------------------------------------------------------------------------------
id name age sex
1 abcd 22 m
2 xyz 24 m
3 rrrrr 22 f
4 eeee 22 f
5 zzz 23 m
Indexes
---------------------
CREATE INDEX "iName" ON "employee_table" ("name" ASC)
EXPLAIN QUERY PLAN SELECT count(id) from employee_table
0 l 0 l 0 l SCAN TABLE employee_table (~1000000 rows)
EXPLAIN QUERY PLAN SELECT count(id) from employee_table WHERE id IS NOT NULL
0 l 0 l 0 l SCAN TABLE employee_table (~500000 rows)
EXPLAIN QUERY PLAN SELECT count(id) from employee_table WHERE id IS NOT NULL
AND id!=0
0 l 0 l 0 l SCAN TABLE employee_table (~250000 rows)
EXPLAIN QUERY PLAN SELECT count(id) from employee_table WHERE id IS NOT NULL
AND id!=0 AND id>0
0 l 0 l 0 l SEARCH TABLE employee_table USING INTEGER PRIMARY KEY
(rowid>?)(~82500 rows)
Everytime I increase the where clause with one more expression, I see the
number of rows get halved.
My emphasis was to reduce the number of rows being traversed to execute this
query.
Is this normal? Or did I interpret it totally wrong? I shall appreciate your
suggestions.
Regards,
Akbar Syed
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
