Re: SQLite and SELECT WHERE local_relpath LIKE

Philip Martin <philip.martin_at_wandisco.com> writes:

> I suppose I will have to try using EXPLAIN.

For the fast query
  "select ... where wc_id = ...
                and local_relpath > ... and local_relpath < ..."
explain starts:

0|Ttace|0|0|0||00|
1|Integer|1|1|0||00|
2|String8|0|2|0|zig1/zag27/|00|
3|String8|0|3|0|zig1/zag270|00|
4|Goto|0|42|0||00|
5|OpenRead|0|10|0|21|00|
6|OpenRead|1|11|0|keyinfo(3,BINARY,BINARY)|00|
7|SCopy|1|4|0||00|
8|Copy|2|5|0||00|
9|SeekGt|1|39|4|2|00|
10|Copy|3|5|0||00|
11|IdxGE|1|39|4|2|00|
12|Column|1|1|6||00|
13|IsNull|6|38|0||00|
14|IdxRowid|1|6|0||00|
15|Seek|0|6|0||00|
...
45|Goto|0|5|0||00|

For the slow query
 "select ... where wc_id = ...
               and local_relpath = ...
               or (local_relpath > ... and local_relpath < ...)"
explain starts:

0|Trace|0|0|0||00|
1|Integer|1|1|0||00|
2|String8|0|2|0|zig1/zag27|00|
3|String8|0|3|0|zig1/zag27/|00|
4|String8|0|4|0|zig1/zag270|00|
5|Goto|0|40|0||00|
6|OpenRead|0|10|0|21|00|
7|Rewind|0|38|0||00|
8|Column|0|0|5||00|
9|Ne|1|12|5|collseq(BINARY)|6c|
10|Column|0|1|6||00|
11|Eq|2|15|6|collseq(BINARY)|61|
12|Column|0|1|6||00|
13|Le|3|37|6|collseq(BINARY)|69|
14|Ge|4|37|6|collseq(BINARY)|69|
...
43|Goto|0|6|0||00|

-- 
Philip