Then the OR clause might be coded such that a separate index is used If every subterm of an OR clause is separately indexable In order to see if the subterm is indexable by itself. LIKE or BETWEEN expressions, or a subtermĬan be a parenthesized list of AND-connected sub-subterms.Įach subterm is analyzed as if it were itself the entire WHERE clause Then two "virtual" terms are added as follows:Įxpr1 >= expr2 AND expr1 y or they can be
If a term of the WHERE clause is of the following form:
However, if three additional indexes where added that contained columnsī, c, and d as their left-most columns, then the
#Planner studio science table full
This query would result in a full table scan. The index is not usable because the WHERE clause terms are connectedīy OR instead of AND. WHERE a=5 OR b IN (1,2,3) OR c NOT NULL OR d='hello' Statement) and everything will work the same.įor the index above and WHERE clause like this: (meaning a copy of the expression that appears in the CREATE INDEX Used in the foregoing text, one can substitute "indexed expression" In the case of indexes on expressions, whenever the word "column" is (See the skip-scan optimization below for the exception.) Similarly, index columns will not normally be used (for indexing purposes)Ĭolumn that is constrained only by inequalities. That constrains column c, then terms that constrain columns a and b canīe used with the index but not terms that constrain columns d through z. Thus for the example index above, if there is no WHERE clause term However, there cannot be gaps in the columns of the index that are used. WHERE clause term in order for that index to be used. It is not necessary for every column of an index to appear in a That must sandwich the allowed values of the column between two extremes. The right-most column that is used can employ inequalities.Ĭolumn of an index that is used, there can be up to two inequalities The initial columns of the index must be used with (columns a, b, and so forth) appear in WHERE clause terms. Then the index might be used if the initial columns of the index If an index is created using a statement like this:ĬREATE INDEX idx_ex1 ON ex1(a,b,c,d,e.,y,z) To be usable by an index a term must be of one of the followingĬolumn = expression column IS expression column > expression column >= expression column column expression >= column expression < column expression <= column column IN ( expression-list ) To which the OR-clause optimization is applied.Īll terms of the WHERE clause are analyzed to see if they can be Operator then the entire clause is considered to be a single "term" If the WHERE clause is composed of constraints separated by the OR Is separated from the others by an AND operator. The WHERE clause on a query is broken up into "terms" where each term How the NGQP differs from the legacy query planner, see the Pre-3.8.0 legacy query planner and to the NGQP. All of the features, techniques,Īnd algorithms described in this document are applicable to both the The SQLite query planner was reimplemented as the Task of the query planner is to select an algorithm from among the manyĬhoices that provides the answer with a minimum of disk I/O and CPUĪdditional background information is available in the Of the statement itself and of the underlying database schema. Thousands of ways to implement that statement, depending on the complexity Given a single SQL statement, there might be dozens, hundreds, or even
This document provides an overview of how the query planner and optimizer
0 kommentar(er)
