We have started using queries of the form
SELECT ... WHERE ... AND local_relpath LIKE ...
and I was curious about the performance of LIKE. So I tried a large
database:
$ sqlite3 wcx.db "select count(*) from nodes"
377021
and a LIKE query:
$ time sqlite3 wcx.db "select count(*) from nodes w
* Philip Martin:
> We have started using queries of the form
>
>SELECT ... WHERE ... AND local_relpath LIKE ...
>
> and I was curious about the performance of LIKE.
LIKE is ASCII-case-insensitive in SQLite, and the indexes are
case-sensitive by default, so SQLite can't do the usual range
opti
Florian Weimer writes:
> * Philip Martin:
>
>> We have started using queries of the form
>>
>>SELECT ... WHERE ... AND local_relpath LIKE ...
>>
>> and I was curious about the performance of LIKE.
>
> LIKE is ASCII-case-insensitive in SQLite, and the indexes are
> case-sensitive by default, s
Philip Martin writes:
> I need something like
>
> local_relpath = 'zig1/zag27
> OR (local_relpath > 'zig1/zag27/' AND local_relpath < 'zig1/zag270')
>
> and that is as slow as LIKE. Adding that "local_relpath =" is the
> problem, without it I get the children in 0.006s. With it I get
* Philip Martin:
>> and that is as slow as LIKE. Adding that "local_relpath =" is the
>> problem, without it I get the children in 0.006s. With it I get the
>> path and the children but it takes 0.35s.
>
> SQL is tricky: I can run a query using either
>
> local_relpath = 'zig1/zag27
>
> or
Florian Weimer writes:
> It seems an optimizer issue. Which version of SQLite do you use?
I was using 3.6.21-2~bpo50 on Debian/stable. I've just built a local
3.7.3-1 and get the same result.
The database has 377021 rows. The exact commands are:
sqlite3 wcx.db "select count(*) from nodes
Philip Martin writes:
> sqlite3 wcx.db "select count(*) from nodes where wc_id = 1 and
> local_relpath = 'zag1/zag27'"
Typo, that should be 'zig1/zag27'.
--
Philip
Philip Martin 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/za
Philip Martin wrote on Fri, Oct 29, 2010 at 08:58:02 +0100:
> Can we treat the string as a bytes and just increment and carry?
No, this might invalidate the UTF-8 sequence. If a character spans
multiple bytes, then those bytes have header bits of the form /^1+0/
(in regex), so incrementing the by
Daniel Shahaf writes:
> Philip Martin wrote on Fri, Oct 29, 2010 at 08:58:02 +0100:
>> Can we treat the string as a bytes and just increment and carry?
>
> No, this might invalidate the UTF-8 sequence. If a character spans
> multiple bytes, then those bytes have header bits of the form /^1+0/
>
* Philip Martin:
>> I suppose I will have to try using EXPLAIN.
As expected, the slow query doesn't use the index. I think you should
bring this up on SQLite user mailing list.
--
Florian Weimer
BFK edv-consulting GmbH http://www.bfk.de/
Kriegsstraße 100 tel:
[Philip Martin]
> sqlite3 wcx.db "select count(*) from nodes where wc_id = 1 and
> local_relpath = 'zag1/zag27'"
>
> sqlite3 wcx.db "select count(*) from nodes where wc_id = 1 and
> (local_relpath > 'zig1/zag27/' and local_relpath < 'zig1/zag270')"
> sqlite3 wcx.db "select count(*) from
On Fri, Oct 29, 2010 at 05:57, Philip Martin wrote:
> Florian Weimer writes:
>
>> It seems an optimizer issue. Which version of SQLite do you use?
>
> I was using 3.6.21-2~bpo50 on Debian/stable. I've just built a local
> 3.7.3-1 and get the same result.
>
> The database has 377021 rows. The e
13 matches
Mail list logo
