Run "analyze" and your numbers will make more sense...albeit still not perfect.
Those numbers are estimations, ergo the "~" in the answer.
Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
________________________________
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Akbar Syed [syed.akba...@googlemail.com]
Sent: Wednesday, August 17, 2011 2:34 PM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] Improving the query optimizer
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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
