When WHERE condition is constant, there are no need to evaluate and check it for
each row. It works, but only partially:
sqlite> explain SELECT * FROM t;
0|Trace|0|0|0||00|
1|Goto|0|17|0||00|
2|OpenRead|0|60|0|9|00|
3|Rewind|0|15|0||00|
4|Column|0|0|1||00|
5|Column|0|1|2||00|
6|Rowid|0|3|0||00|
7|Column|0|3|4||00|
8|Column|0|4|5||00|
9|Column|0|5|6||00|
10|Column|0|6|7||00|
11|Column|0|7|8||00|
12|Column|0|8|9|0|00|
13|ResultRow|1|9|0||00|
14|Next|0|4|0||01|
15|Close|0|0|0||00|
16|Halt|0|0|0||00|
...
sqlite> explain SELECT * FROM t;
0|Trace|0|0|0||00|
1|Integer|1|1|0||00|
2|IfNot|1|18|1||00|
3|Goto|0|20|0||00|
4|OpenRead|0|60|0|9|00|
5|Rewind|0|18|0||00|
6|IfNot|1|17|1||00|
7|Column|0|0|3||00|
8|Column|0|1|4||00|
9|Rowid|0|5|0||00|
10|Column|0|3|6||00|
11|Column|0|4|7||00|
12|Column|0|5|8||00|
13|Column|0|6|9||00|
14|Column|0|7|10||00|
15|Column|0|8|11|0|00|
16|ResultRow|3|9|0||00|
17|Next|0|6|0||01|
18|Close|0|0|0||00|
19|Halt|0|0|0||00|
[...]
Look at addr 6, there are completely unnecessary IfNot inside of loop: this
condition is already checked out-of-loop.
I've looked at code, and noticed strange thing:
src/where.c, line 4631:
/* Special case: a WHERE clause that is constant. Evaluate the
** expression and either jump over all of the code or fall thru.
*/
if( pWhere && (nTabList==0 || sqlite3ExprIsConstantNotJoin(pWhere)) ){
sqlite3ExprIfFalse(pParse, pWhere, pWInfo->iBreak, SQLITE_JUMPIFNULL);
pWhere = 0;
}
But pWhere *is not used* in any code below this fragment, only *above* this
code. Patch below.
[In fact, you can move out out loop not only *whole* constant WHERE, but also
all constant AND terms of WHERE, like this:
SELECT * FROM t WHERE const1 AND notconst AND const2 ->
SELECT * FROM (SELECT * FROM t WHERE notconst) WHERE const1 AND const2
I'll take a shot on that later.]
The author or authors of this code dedicate any and all copyright interest
in this code to the public domain. We make this dedication for the benefit
of the public at large and to the detriment of our heirs and successors.
We intend this dedication to be an overt act of relinquishment in perpetuity
of all present and future rights to this code under copyright law.
Signed-off-by: Yuriy M. Kaminskiy <yum...@gmail.com>
Index: sqlite3-3.7.8/src/where.c
===================================================================
--- sqlite3-3.7.8.orig/src/where.c 2011-10-23 20:04:58.000000000 +0400
+++ sqlite3-3.7.8/src/where.c 2011-10-23 20:06:30.000000000 +0400
@@ -4625,7 +4625,6 @@ WhereInfo *sqlite3WhereBegin(
initMaskSet(pMaskSet);
whereClauseInit(pWC, pParse, pMaskSet);
sqlite3ExprCodeConstants(pParse, pWhere);
- whereSplit(pWC, pWhere, TK_AND); /* IMP: R-15842-53296 */
/* Special case: a WHERE clause that is constant. Evaluate the
** expression and either jump over all of the code or fall thru.
@@ -4635,6 +4634,8 @@ WhereInfo *sqlite3WhereBegin(
pWhere = 0;
}
+ whereSplit(pWC, pWhere, TK_AND); /* IMP: R-15842-53296 */
+
/* Assign a bit from the bitmask to every term in the FROM clause.
**
** When assigning bitmask values to FROM clause cursors, it must be
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
