On 17 Jan 2018, at 3:52am, Nick <haveagoodtime2...@gmail.com> wrote:
> b INTEGER NOT NULL UNIQUE,
[…]
> UNIQUE(b, i)
The second constraint is redundant. If values of b are unique, then so is
anything that includes values of b.
> And I’ve got some speed issues when I query the db:
> SELECT b, c, d, e, f, g, h FROM t1 WHERE b >= 10000;
>
> It needs almost 60ms as there are about 100 records with some long TEXT data
> in the TEXT columns.
>
> I am wondering if it is needed to add ANY INDEX to improve the performance
> of the SELECT?
60ms is a reasonable figure here. 600ms might make be wonder what’s going on.
In order to enforce your "b UNIQUE" constraint, SQLite should have made up its
own index, and it should be using that index when executing your SELECT.
Here’s how to make sure. Do the following in the SQLite command-line tool.
1) If possible, put some plausible data in the table. If this is not
appropriate, that’s okay, just leave the table empty.
2) Execute "ANALYZE;". The results will be saved with the database file.
There’s no need to do "ANALYZE;" again unless you add or delete tables or
indexes.
3) Add "EXPLAIN QUERY PLAN " to the beginning of your "SELECT …" command and
see what SQLite shows.
EXPLAIN QUERY PLAN SELECT b, c, d, e, f, g, h FROM t1 WHERE b >= 10000;
SQLite should output something which includes either "search" or "scan". If it
includes "search" then it’s using an index to access the right records, so
it’ll be fast. If it includes "scan" then it’s reading an entire table or
index, so it’ll be slow.
In your case, you did it right. You will see that the index it chose includes
just the values of b, so long TEXT values aren’t a problem here.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
