count registers in a table

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

count registers in a table

MONSTRUO Hugo González
Hi,


Which is the fastest way to count the records of a table. ? And records
that meet a condition?

regards
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: count registers in a table

Simon Slavin-3

On 24 Dec 2016, at 10:52am, MONSTRUO Hugo González <[hidden email]> wrote:

> Which is the fastest way to count the records of a table. ? And records
> that meet a condition?

SELECT COUNT(*) FROM MyTable


SELECT COUNT(*) FROM MyTable WHERE a = b


There is special optimization for COUNT with a * which can sometimes speed these enquiries up quite a bit.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: count registers in a table

MONSTRUO Hugo González
In reply to this post by MONSTRUO Hugo González
> Which is the fastest way to count the records of a table. ? And records
> that meet a condition?

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

SELECT COUNT(RowId) FROM MyTable WHERE a LIKE '%abc%' ORDER BY PrimaryIndex
<< is FAST with more registers, but is SLOWLY with 726000 registers

Which is the fastest way to count the records of a table with a condition
and 700.000 registers ?????


Which is the fastest way to select 23 registers that meet a condition ?????

Hugo
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: count registers in a table

Donald Griggs
Hello, Hugo,

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
late 2013.
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.

http://stackoverflow.com/questions/2869135/sqlite-trigger-to-update-summary-counts


3) Regarding:  "SELECT COUNT(RowId) FROM MyTable WHERE a LIKE '%abc%' ORDER
BY PrimaryIndex
<< 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.

Perhaps:
     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
condition ?????"

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
expressions.

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.


Regards,
   Donald
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: count registers in a table

Simon Slavin-3
In reply to this post by MONSTRUO Hugo González

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

SELECT sqlite_version()

Please also post the result of

PRAGMA integrity_check

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.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users