Debian Codegraph @

www/lang_indexedby.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>The INDEXED BY Clause</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 The INDEXED BY Clause
122 </div>
123 </div>
124
125
126
127
128 <h1 id="how_indexed_by_works"><span>1. </span>How INDEXED BY Works</h1>
129
130 <p>The INDEXED BY phrase forces the <a href="optoverview.html">SQLite query planner</a> to use a
131 particular named index on a <a href="lang_delete.html">DELETE</a>, <a href="lang_select.html">SELECT</a>, or <a href="lang_update.html">UPDATE</a> statement.
132 The INDEXED BY phrase is an SQLite extension and
133 is not portable to other SQL database engines.</p>
134
135 <p><b><a href="syntax/qualified-table-name.html">qualified-table-name:</a></b>
136 <button id='xa98d816f' onclick='hideorshow("xa98d816f","xa8511163")'>hide</button></p>
137 <div id='xa8511163' class='imgcontainer'>
138 <div style="max-width:599px"><svg xmlns='http://www.w3.org/2000/svg' style='font-size:initial;' class="pikchr" viewBox="0 0 599.746 182.736" data-pikchr-date="20250319161943">
139 <circle cx="5.76" cy="17.28" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
140 <polygon points="45.36,17.28 33.84,21.6 33.84,12.96" style="fill:rgb(0,0,0)"/>
141 <path d="M9.36,17.28L39.6,17.28" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
142 <path d="M60.48,32.4L156.845,32.4A15.12 15.12 0 0 0 171.965 17.28A15.12 15.12 0 0 0 156.845 2.16L60.48,2.16A15.12 15.12 0 0 0 45.36 17.28A15.12 15.12 0 0 0 60.48 32.4Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
143 <text x="108.662" y="17.28" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">schema-name</text>
144 <polygon points="195.005,17.28 183.485,21.6 183.485,12.96" style="fill:rgb(0,0,0)"/>
145 <path d="M171.965,17.28L189.245,17.28" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
146 <path d="M210.125,32.4A15.12 15.12 0 0 0 225.245 17.28A15.12 15.12 0 0 0 210.125 2.16A15.12 15.12 0 0 0 195.005 17.28A15.12 15.12 0 0 0 210.125 32.4Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
147 <text x="210.125" y="17.28" text-anchor="middle" font-weight="bold" fill="rgb(0,0,0)" dominant-baseline="central">.</text>
148 <polygon points="261.245,17.28 249.725,21.6 249.725,12.96" style="fill:rgb(0,0,0)"/>
149 <path d="M225.245,17.28L255.485,17.28" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
150 <path d="M276.365,32.4L351.533,32.4A15.12 15.12 0 0 0 366.653 17.28A15.12 15.12 0 0 0 351.533 2.16L276.365,2.16A15.12 15.12 0 0 0 261.245 17.28A15.12 15.12 0 0 0 276.365 32.4Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
151 <text x="313.949" y="17.28" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">table-name</text>
152 <polygon points="408.173,44.496 396.653,48.816 396.653,40.176" style="fill:rgb(0,0,0)"/>
153 <path d="M366.653,17.28 L 374.153,17.28 Q 381.653,17.28 381.653,30.888 Q 381.653,44.496 392.033,44.496 L 402.413,44.496" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
154 <path d="M423.293,59.616L427.267,59.616A15.12 15.12 0 0 0 442.387 44.496L442.387,44.496A15.12 15.12 0 0 0 427.267 29.376L423.293,29.376A15.12 15.12 0 0 0 408.173 44.496L408.173,44.496A15.12 15.12 0 0 0 423.293 59.616Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
155 <text x="425.28" y="44.496" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">AS</text>
156 <polygon points="465.427,44.496 453.907,48.816 453.907,40.176" style="fill:rgb(0,0,0)"/>
157 <path d="M442.387,44.496L459.667,44.496" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
158 <path d="M480.547,59.616L501.226,59.616A15.12 15.12 0 0 0 516.346 44.496L516.346,44.496A15.12 15.12 0 0 0 501.226 29.376L480.547,29.376A15.12 15.12 0 0 0 465.427 44.496L465.427,44.496A15.12 15.12 0 0 0 480.547 59.616Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
159 <text x="490.886" y="44.496" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">alias</text>
160 <polygon points="539.386,44.496 527.866,48.816 527.866,40.176" style="fill:rgb(0,0,0)"/>
161 <path d="M516.346,44.496L533.626,44.496" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
162 <path d="M539.386,44.496 L 546.886,44.496 Q 554.386,44.496 554.386,51.996 L 554.386,59.496" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
163 <polygon points="453.907,17.28 442.387,21.6 442.387,12.96" style="fill:rgb(0,0,0)"/>
164 <path d="M366.653,17.28L448.147,17.28" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
165 <polygon points="108.662,44.496 97.1424,48.816 97.1424,40.176" style="fill:rgb(0,0,0)"/>
166 <path d="M9.36,17.28 L 16.86,17.28 Q 24.36,17.28 24.36,30.888 Q 24.36,44.496 39.36,44.496 L 87.9024,44.496 L 102.902,44.496" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
167 <path d="M108.662,44.496 L 225.245,44.496 Q 240.245,44.496 240.245,30.888 Q 240.245,17.28 247.745,17.28 L 255.245,17.28" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
168 <path d="M236.645,142.776L301.214,142.776A15.12 15.12 0 0 0 316.334 127.656A15.12 15.12 0 0 0 301.214 112.536L236.645,112.536A15.12 15.12 0 0 0 221.525 127.656A15.12 15.12 0 0 0 236.645 142.776Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
169 <text x="268.93" y="127.656" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">INDEXED</text>
170 <polygon points="339.374,127.656 327.854,131.976 327.854,123.336" style="fill:rgb(0,0,0)"/>
171 <path d="M316.334,127.656L333.614,127.656" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
172 <path d="M354.494,142.776L358.584,142.776A15.12 15.12 0 0 0 373.704 127.656A15.12 15.12 0 0 0 358.584 112.536L354.494,112.536A15.12 15.12 0 0 0 339.374 127.656A15.12 15.12 0 0 0 354.494 142.776Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
173 <text x="356.539" y="127.656" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">BY</text>
174 <polygon points="396.744,127.656 385.224,131.976 385.224,123.336" style="fill:rgb(0,0,0)"/>
175 <path d="M373.704,127.656L390.984,127.656" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
176 <path d="M411.864,142.776L490.258,142.776A15.12 15.12 0 0 0 505.378 127.656A15.12 15.12 0 0 0 490.258 112.536L411.864,112.536A15.12 15.12 0 0 0 396.744 127.656A15.12 15.12 0 0 0 411.864 142.776Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
177 <text x="451.061" y="127.656" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">index-name</text>
178 <path d="M236.645,180.576L256.747,180.576A15.12 15.12 0 0 0 271.867 165.456A15.12 15.12 0 0 0 256.747 150.336L236.645,150.336A15.12 15.12 0 0 0 221.525 165.456A15.12 15.12 0 0 0 236.645 180.576Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
179 <text x="246.696" y="165.456" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">NOT</text>
180 <polygon points="294.907,165.456 283.387,169.776 283.387,161.136" style="fill:rgb(0,0,0)"/>
181 <path d="M271.867,165.456L289.147,165.456" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
182 <path d="M310.027,180.576L374.597,180.576A15.12 15.12 0 0 0 389.717 165.456A15.12 15.12 0 0 0 374.597 150.336L310.027,150.336A15.12 15.12 0 0 0 294.907 165.456A15.12 15.12 0 0 0 310.027 180.576Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
183 <text x="342.312" y="165.456" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">INDEXED</text>
184 <polygon points="554.386,74.496 550.066,62.976 558.706,62.976" style="fill:rgb(0,0,0)"/>
185 <path d="M453.907,17.28 L 539.386,17.28 Q 554.386,17.28 554.386,32.28 L 554.386,53.736 L 554.386,68.736" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
186 <polygon points="313.949,89.856 325.469,85.536 325.469,94.176" style="fill:rgb(0,0,0)"/>
187 <path d="M554.386,74.496 L 554.386,82.176 Q 554.386,89.856 539.386,89.856 L 334.709,89.856 L 319.709,89.856" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
188 <polygon points="221.525,127.656 210.005,131.976 210.005,123.336" style="fill:rgb(0,0,0)"/>
189 <path d="M313.949,89.856 L 210.005,89.856 Q 195.005,89.856 195.005,104.856 L 195.005,112.656 Q 195.005,127.656 205.385,127.656 L 215.765,127.656" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
190 <polygon points="221.525,165.456 210.005,169.776 210.005,161.136" style="fill:rgb(0,0,0)"/>
191 <path d="M195.005,112.536 L 195.005,150.456 Q 195.005,165.456 205.385,165.456 L 215.765,165.456" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
192 <polygon points="554.386,114.516 550.066,102.996 558.706,102.996" style="fill:rgb(0,0,0)"/>
193 <path d="M554.386,74.496L554.386,108.756" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
194 <polygon points="590.386,165.456 578.866,169.776 578.866,161.136" style="fill:rgb(0,0,0)"/>
195 <path d="M554.386,114.516 L 554.386,150.456 Q 554.386,165.456 569.386,165.456 L 569.626,165.456 L 584.626,165.456" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
196 <circle cx="593.986" cy="165.456" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
197 <polygon points="539.386,127.656 527.866,131.976 527.866,123.336" style="fill:rgb(0,0,0)"/>
198 <path d="M505.378,127.656L533.626,127.656" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
199 <path d="M539.386,127.656 L 546.886,127.656 Q 554.386,127.656 554.386,135.156 L 554.386,142.656" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
200 <path d="M389.717,165.456L578.866,165.456" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
201 </svg>
202 </div>
203 </div>
204
205
206 <p>The "INDEXED BY <span class='yyterm'>index-name</span>" phrase specifies
207 that the named index
208 must be used in order to look up values on the preceding table.
209 If <span class='yyterm'>index-name</span> does not exist or cannot be used
210 for the query, then the preparation of the SQL statement fails.
211 The "NOT INDEXED" clause specifies that no index shall be used when
212 accessing the preceding table, including implied indices create by
213 UNIQUE and PRIMARY KEY constraints. However, the <a href="lang_createtable.html#rowid">rowid</a>
214 can still be used to look up entries even when "NOT INDEXED" is specified.</p>
215
216 <p>Some SQL database engines provide non-standard "hint" mechanisms which
217 can be used to give the query optimizer clues about what indices it should
218 use for a particular statement. The INDEXED BY clause of SQLite is
219 <em>not</em> a hinting mechanism and it should not be used as such.
220 The INDEXED BY clause does not give the optimizer hints about which index
221 to use; it gives the optimizer a requirement of which index to use.
222 If the query optimizer is unable to use the index specified by the
223 INDEXED BY clause, then the query will fail with an error.</p>
224
225 <p>The INDEXED BY clause is <em>not</em> intended for use in tuning
226 the performance of a query. The intent of the INDEXED BY clause is
227 to raise a run-time error if a schema change, such as dropping or
228 creating an index, causes the query plan for a time-sensitive query
229 to change. The INDEXED BY clause is designed to help detect
230 undesirable query plan changes during regression testing.
231 Application
232 developers are admonished to omit all use of INDEXED BY during
233 application design, implementation, testing, and tuning. If
234 INDEXED BY is to be used at all, it should be inserted at the very
235 end of the development process when "locking down" a design.</p>
236
237 <h1 id="see_also"><span>2. </span>See Also</h1>
238
239 <ol>
240 <li><p>The <a href="queryplanner-ng.html#howtofix">query planner checklist</a> describes steps that application
241 developers should follow to resolve query planner problems.
242 Notice the that the use of INDEXED BY is a last resort, to be used only
243 when all other measures fail.</p>
244
245 </li><li><p><a href="optoverview.html#uplus">The unary "+" operator</a>
246 can be used to disqualify terms in the WHERE clause from use by indices.
247 Careful use of unary + can sometimes help prevent the query planner from
248 choosing a poor index without restricting it to using one specific index.
249 Careful placement of unary + operators is a better method for controlling
250 which indices are used by a query.</p>
251
252 </li><li><p>The <a href="c3ref/stmt_status.html">sqlite3_stmt_status()</a> C/C++ interface together with the
253 <a href="c3ref/c_stmtstatus_counter.html#sqlitestmtstatusfullscanstep">SQLITE_STMTSTATUS_FULLSCAN_STEP</a> and <a href="c3ref/c_stmtstatus_counter.html#sqlitestmtstatussort">SQLITE_STMTSTATUS_SORT</a> verbs
254 can be used to detect at run-time when an SQL statement is not
255 making effective use of indices. Many applications may prefer to
256 use the <a href="c3ref/stmt_status.html">sqlite3_stmt_status()</a> interface to detect index misuse
257 rather than the INDEXED BY phrase described here.</p>
258 </li></ol>
259 <p align="center"><small><i>This page was last updated on 2025-07-12 15:11:36Z </small></i></p>
260