On 2012/04/08 Yuriy Kaminskiy wrote:
> On 2011/10/23, Yuriy Kaminskiy wrote:
>> Yuriy Kaminskiy wrote:
>>> Yuriy Kaminskiy wrote:
>>>> Yuriy Kaminskiy wrote:
>>>>> When WHERE condition is constant, there are no need to evaluate and check
>>>>> it for
>>>>> each row. It works, but only partially:
>>>> ...
>>>>> [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.]
>>>> Here it goes.
>>>>
>>>> Prerequisite: previous patch.
>>>> Passes quick regression test (make test).
>>>> Possible problem: short-circuits evaluation. Should not be a problem, IMO,
>>>> as only
>>>> constants references? Please verify.
>>> Ping.
>> Ping.
> Ping.
> For convenience all 3 patches collected below (needed no change for 3.7.11).
Ping. Over 2 years passed since this patch series was first posted.
Updated patch series for 3.8.1 below.
--
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>
Part 1: Move whereSplit() to unbreak constant condition elimination.
Test case:
CREATE TABLE t (i, j, k);
EXPLAIN SELECT * FROM t WHERE 11
Index: sqlite3-3.8.1/src/where.c
===================================================================
--- sqlite3-3.8.1.orig/src/where.c 2013-10-17 22:57:30.000000000 +0400
+++ sqlite3-3.8.1/src/where.c 2013-11-03 22:44:41.000000000 +0400
@@ -5727,7 +5727,6 @@ WhereInfo *sqlite3WhereBegin(
initMaskSet(pMaskSet);
whereClauseInit(&pWInfo->sWC, pWInfo);
sqlite3ExprCodeConstants(pParse, pWhere);
- whereSplit(&pWInfo->sWC, pWhere, TK_AND);
sqlite3CodeVerifySchema(pParse, -1); /* Insert the cookie verifier Goto */
/* Special case: a WHERE clause that is constant. Evaluate the
@@ -5738,6 +5737,8 @@ WhereInfo *sqlite3WhereBegin(
pWhere = 0;
}
+ whereSplit(&pWInfo->sWC, pWhere, TK_AND);
+
/* Special case: No FROM clause
*/
if( nTabList==0 ){
===================================================================
Part 2: optimize "WHERE const AND notconst" too
Test case:
EXPLAIN SELECT * FROM t WHERE 11 AND 12 AND i AND 13 AND j AND 14;
Index: sqlite3-3.8.1/src/where.c
===================================================================
--- sqlite3-3.8.1.orig/src/where.c 2013-11-03 23:27:05.000000000 +0400
+++ sqlite3-3.8.1/src/where.c 2013-11-03 23:27:59.000000000 +0400
@@ -5739,6 +5739,24 @@ WhereInfo *sqlite3WhereBegin(
whereSplit(&pWInfo->sWC, pWhere, TK_AND);
+ {
+ /* Move const in "WHERE const AND notconst" out of internal loop */
+ int i, j;
+ WhereClause * const pWC = &pWInfo->sWC;
+
+ for(j=i=0; i<pWC->nTerm; i++){
+ if( nTabList==0 || sqlite3ExprIsConstantNotJoin(pWC->a[i].pExpr) ){
+ sqlite3ExprIfFalse(pParse, pWC->a[i].pExpr, pWInfo->iBreak,
SQLITE_JUMPIFNULL);
+ continue;
+ }
+ if( j!=i )
+ pWC->a[j]=pWC->a[i];
+ j++;
+ }
+ pWC->nTerm -= i-j;
+ /*if (i != j) memset(&(pWC->a[j]), 0, (i-j)*sizeof(pWC->a[0]));*/
+ }
+
/* Special case: No FROM clause
*/
if( nTabList==0 ){
===================================================================
Part 3: Remove now-redundant sqlite3ExprIsConstantNotJoin call.
There are minor change:
EXPLAIN SELECT * FROM t WHERE 1 AND 2
Index: sqlite3-3.8.1/src/where.c
===================================================================
--- sqlite3-3.8.1.orig/src/where.c 2013-11-03 22:54:44.000000000 +0400
+++ sqlite3-3.8.1/src/where.c 2013-11-03 22:56:18.000000000 +0400
@@ -5727,20 +5727,13 @@ WhereInfo *sqlite3WhereBegin(
initMaskSet(pMaskSet);
whereClauseInit(&pWInfo->sWC, pWInfo);
sqlite3ExprCodeConstants(pParse, pWhere);
- sqlite3CodeVerifySchema(pParse, -1); /* Insert the cookie verifier Goto */
-
- /* 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;
- }
-
whereSplit(&pWInfo->sWC, pWhere, TK_AND);
+ sqlite3CodeVerifySchema(pParse, -1); /* Insert the cookie verifier Goto */
{
- /* Move const in "WHERE const AND notconst" out of internal loop */
+ /* Special case: AND subterm of WHERE clause that is constant. Evaluate the
+ ** expression and either jump over all of the code or fall thru.
+ */
int i, j;
WhereClause * const pWC = &pWInfo->sWC;
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
