Debian Codegraph @

www/expridx.html

raw
1 <!DOCTYPE html>
2 <html><head>
3 <meta name="viewport" content="width=device-width, initial-scale=1.0">
4 <meta http-equiv="content-type" content="text/html; charset=UTF-8">
5 <link href="sqlite.css" rel="stylesheet">
6 <title>Indexes On Expressions</title>
7 <!-- path= -->
8 </head>
9 <body>
10 <div class=nosearch>
11 <a href="index.html">
12 <img class="logo" src="images/sqlite370_banner.svg" alt="SQLite" border="0">
13 </a>
14 <div><!-- IE hack to prevent disappearing logo --></div>
15 <div class="tagline desktoponly">
16 Small. Fast. Reliable.<br>Choose any three.
17 </div>
18 <div class="menu mainmenu">
19 <ul>
20 <li><a href="index.html">Home</a>
21 <li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
22 <li class='wideonly'><a href='about.html'>About</a>
23 <li class='desktoponly'><a href="docs.html">Documentation</a>
24 <li class='desktoponly'><a href="download.html">Download</a>
25 <li class='wideonly'><a href='copyright.html'>License</a>
26 <li class='desktoponly'><a href="support.html">Support</a>
27 <li class='desktoponly'><a href="prosupport.html">Purchase</a>
28 <li class='search' id='search_menubutton'>
29 <a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
30 </ul>
31 </div>
32 <div class="menu submenu" id="submenu">
33 <ul>
34 <li><a href='about.html'>About</a>
35 <li><a href='docs.html'>Documentation</a>
36 <li><a href='download.html'>Download</a>
37 <li><a href='support.html'>Support</a>
38 <li><a href='prosupport.html'>Purchase</a>
39 </ul>
40 </div>
41 <div class="searchmenu" id="searchmenu">
42 <form method="GET" action="search">
43 <select name="s" id="searchtype">
44 <option value="d">Search Documentation</option>
45 <option value="c">Search Changelog</option>
46 </select>
47 <input type="text" name="q" id="searchbox" value="">
48 <input type="submit" value="Go">
49 </form>
50 </div>
51 </div>
52 <script>
53 function toggle_div(nm) {
54 var w = document.getElementById(nm);
55 if( w.style.display=="block" ){
56 w.style.display = "none";
57 }else{
58 w.style.display = "block";
59 }
60 }
61 function toggle_search() {
62 var w = document.getElementById("searchmenu");
63 if( w.style.display=="block" ){
64 w.style.display = "none";
65 } else {
66 w.style.display = "block";
67 setTimeout(function(){
68 document.getElementById("searchbox").focus()
69 }, 30);
70 }
71 }
72 function div_off(nm){document.getElementById(nm).style.display="none";}
73 window.onbeforeunload = function(e){div_off("submenu");}
74 /* Disable the Search feature if we are not operating from CGI, since */
75 /* Search is accomplished using CGI and will not work without it. */
76 if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
77 document.getElementById("search_menubutton").style.display = "none";
78 }
79 /* Used by the Hide/Show button beside syntax diagrams, to toggle the */
80 function hideorshow(btn,obj){
81 var x = document.getElementById(obj);
82 var b = document.getElementById(btn);
83 if( x.style.display!='none' ){
84 x.style.display = 'none';
85 b.innerHTML='show';
86 }else{
87 x.style.display = '';
88 b.innerHTML='hide';
89 }
90 return false;
91 }
92 var antiRobot = 0;
93 function antiRobotGo(){
94 if( antiRobot!=3 ) return;
95 antiRobot = 7;
96 var j = document.getElementById("mtimelink");
97 if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
98 }
99 function antiRobotDefense(){
100 document.body.onmousedown=function(){
101 antiRobot |= 2;
102 antiRobotGo();
103 document.body.onmousedown=null;
104 }
105 document.body.onmousemove=function(){
106 antiRobot |= 2;
107 antiRobotGo();
108 document.body.onmousemove=null;
109 }
110 setTimeout(function(){
111 antiRobot |= 1;
112 antiRobotGo();
113 }, 100)
114 antiRobotGo();
115 }
116 antiRobotDefense();
117 </script>
118 <div class=fancy>
119 <div class=nosearch>
120 <div class="fancy_title">
121 Indexes On Expressions
122 </div>
123 <details class="fancy_toc">
124 <summary>Table Of Contents</summary>
125 <div id="toc_sub"><div class="fancy-toc1"><a href="#how_to_use_indexes_on_expressions">1. How To Use Indexes On Expressions</a></div>
126 <div class="fancy-toc1"><a href="#restrictions">2. Restrictions</a></div>
127 <div class="fancy-toc1"><a href="#compatibility">3. Compatibility</a></div>
128 </div>
129 </details>
130 </div>
131
132
133
134
135
136 <p>
137 Normally, an SQL index references columns of a table. But an index
138 can also be formed on expressions involving table columns, either
139 directly by writing an expression instead of a bare column name in
140 the CREATE INDEX statement, or by having the CREATE INDEX reference
141 a <a href="gencol.html#vgc*">VIRTUAL generated column</a>.
142
143 </p><p>
144 As an example, consider the following table that tracks
145 dollar-amount changes on various "accounts":
146
147 </p><div class="codeblock"><pre>CREATE TABLE account_change(
148 chng_id INTEGER PRIMARY KEY,
149 acct_no INTEGER REFERENCES account,
150 location INTEGER REFERENCES locations,
151 amt INTEGER, -- in cents
152 authority TEXT,
153 comment TEXT
154 );
155 CREATE INDEX acctchng_magnitude ON account_change(acct_no, abs(amt));
156 </pre></div>
157
158 <p>
159 Each entry in the account_change table records a deposit or a withdrawal
160 into an account. Deposits have a positive "amt" and withdrawals have
161 a negative "amt".
162
163 </p><p>
164 The acctchng_magnitude index is over the account number ("acct_no") and
165 on the absolute value of the amount. This index allows one to do
166 efficient queries over the magnitude of a change to the account.
167 For example, to list all changes to account number $xyz that are
168 more than $100.00, one can say:
169
170
171 </p><div class="codeblock"><pre>SELECT * FROM account_change WHERE acct_no=$xyz AND abs(amt)>=10000;
172 </pre></div>
173
174 <p>
175 Or, to list all changes to one particular account ($xyz) in order of
176 decreasing magnitude, one can write:
177
178 </p><div class="codeblock"><pre>SELECT * FROM account_change WHERE acct_no=$xyz
179 ORDER BY abs(amt) DESC;
180 </pre></div>
181
182 <p>
183 Both of the above example queries would work fine without the
184 acctchng_magnitude index.
185 The acctchng_magnitude index merely helps the queries to run
186 faster, especially on databases where there are many entries in
187 the table for each account.
188
189 </p><h1 id="how_to_use_indexes_on_expressions"><span>1. </span>How To Use Indexes On Expressions</h1>
190
191 <p>
192 Use a <a href="lang_createindex.html">CREATE INDEX</a> statement to create a new index on one or more
193 expressions just like you would to create an index on columns. The only
194 difference is that expressions are listed as the elements to be indexed
195 rather than column names.
196
197 </p><p>
198 The SQLite query planner will consider using an index on an expression
199 when the expression that is indexed appears in the WHERE clause or in
200 the ORDER BY clause of a query, <i>exactly</i> as it is written in the
201 CREATE INDEX statement. The query planner does not do algebra. In order
202 to match WHERE clause constraints and ORDER BY terms to indexes, SQLite
203 requires that the expressions be the same, except for minor syntactic
204 differences such as white-space changes. So if you have:
205
206 </p><div class="codeblock"><pre>CREATE TABLE t2(x,y,z);
207 CREATE INDEX t2xy ON t2(x+y);
208 </pre></div>
209
210 <p>
211 And then you run the query:
212
213 </p><div class="codeblock"><pre>SELECT * FROM t2 WHERE y+x=22;
214 </pre></div>
215
216 <p>
217 Then the index will <u>not</u> be used because
218 the expression on the CREATE INDEX
219 statement (x+y) is not the same as the expression as it appears in the
220 query (y+x). The two expressions might be mathematically equivalent, but
221 the SQLite query planner insists that they be the same, not merely
222 equivalent. Consider rewriting the query thusly:
223
224 </p><div class="codeblock"><pre>SELECT * FROM t2 WHERE x+y=22;
225 </pre></div>
226
227 <p>
228 This second query will likely use the index because now the expression
229 in the WHERE clause (x+y) matches the expression in the index exactly.
230
231
232 </p><h1 id="restrictions"><span>2. </span>Restrictions</h1>
233
234 <p>
235 There are certain reasonable restrictions on expressions that appear in
236 CREATE INDEX statements:
237
238 </p><ol>
239 <li><p>
240 Expressions in CREATE INDEX statements
241 may only refer to columns of the table being indexed, not to
242 columns in other tables.
243
244 </p></li><li><p>
245 Expressions in CREATE INDEX statements
246 may contain function calls, but only to functions whose output
247 is always determined completely by its input parameters (a.k.a.:
248 <a href="deterministic.html">deterministic functions</a>). Obviously, functions like <a href="lang_corefunc.html#random">random()</a> will not
249 work well in an index. But also functions like <a href="lang_corefunc.html#sqlite_version">sqlite_version()</a>, though
250 they are constant across any one database connection, are not constant
251 across the life of the underlying database file, and hence may not be
252 used in a CREATE INDEX statement.
253 Note that <a href="appfunc.html">application-defined SQL functions</a> are by default considered
254 non-deterministic and may not be used in a CREATE INDEX statement unless
255 the <a href="c3ref/c_deterministic.html#sqlitedeterministic">SQLITE_DETERMINISTIC</a> flag is used when the function is registered.
256 </p><p>
257 See also: <a href="staleexpridx.html">stale expression indexes</a>
258
259 </p></li><li><p>
260 Expressions in CREATE INDEX statements may not use subqueries.
261
262 </p></li><li><p>
263 Expressions may only be used in CREATE INDEX statements, not within
264 <a href="lang_createtable.html#uniqueconst">UNIQUE</a> or <a href="lang_createtable.html#primkeyconst">PRIMARY KEY</a> constraints within the <a href="lang_createtable.html">CREATE TABLE</a> statement.
265 </p></li></ol>
266
267 <h1 id="compatibility"><span>3. </span>Compatibility</h1>
268
269 <p>
270 The ability to index expressions was added to SQLite with
271 <a href="releaselog/3_9_0.html">version 3.9.0</a> (2015-10-14). A database that uses an index on
272 expressions will not be usable by earlier versions of SQLite.
273 </p><p align="center"><small><i>This page was last updated on 2026-03-20 13:23:44Z </small></i></p>
274