Regarding: "I have a table with 726.000 registers."
1) I assume that you mean what others call "rows" correct? (and not
columns, I hope)
2) Regarding: "SELECT COUNT(*) FROM MyTable << is very slowly"
As I understand it, that should be as fast as SELECT COUNT (RowId) as of
What version of sqlite are you using?
You can make SELECT COUNT(*) FROM MyTable be almost instantaneous, at the
cost of a little effort and a slight slowdown with inserts and deletes, by
maintaining the current row count in the database, and creating TRIGGERs to
keep it current.
3) Regarding: "SELECT COUNT(RowId) FROM MyTable WHERE a LIKE '%abc%' ORDER
<< is FAST with more registers, but is SLOWLY with 726000 registers"
You don't mention whether you have an index defined on column "a", but even
if you did, using LIKE with a wildcard (%) at the beginning means that
SQLite cannot use an index and must scan the entire table.
a) If you only have to search for a few known strings like %abc%, then
you could check for matches at the time rows are inserted, and update a new
column that signifies, e.g., "contains a match for %abc%" and create
indexes on those new columns.
b) If the above is not feasible, you might want to look into SQLite's
FTS feature (full text search).
4) Regarding: "Which is the fastest way to select 23 registers that meet a
Simon Slavin answered that in his reply to you two days ago:
SELECT COUNT(*) FROM MyTable WHERE a = b
and, of course, you would expect a great speed increase by having either
column "a" or column "b" indexed, but remember the caveat for LIKE
5) In general:
-- Consider whether defining more sqlite cache would help.
-- Be sure you have useful indexes defined (and remove non-useful ones.
Use EXPLAIN QUERY PLAN)
-- Be sure to use TRANSACTIONs around multiple statements to be executed as
a unit -- this won't speed up individual SELECTS that you asked about, but
can make such a tremendous speedup for other things -- such as multiple
inserts -- that I'm mentioning it.
On 26 Dec 2016, at 3:14pm, MONSTRUO Hugo González <[hidden email]> wrote:
> I have a table with 726.000 registers.
> SELECT COUNT(*) FROM MyTable << is very slowly
> SELECT COUNT (RowId) FROM MyTable ORDER BY PrimaryIndex << is very FAST
While this is not a bug in SQLite, since it reaches the right answer, there is definitely something wrong with this. Can you please check to see that you get similar timings in the SQLite shell tool. If you do, please post the result of
Please also post the result of
To answer the questions in your post, using COUNT(*) is meant to always be the fastest way to count rows. If it’s not, something is wrong. Which appears to be the case with your own setup.