On Jun 14, 2005, at 8:21 AM, Paul Smith wrote:
At 14:06 14/06/2005, you wrote:
If I would use SELECT COUNT(*) then I have to ask the query again,
right?
E.g:
First I have to do:
SELECT * FROM data WHERE Foo == "bar" LIMIT 1000;
to get the data and then
SELECT COUNT(*) FROM data WHERE Foo == "bar";
to get the total lnumber of lines.
The problem is that the database is very large and the query can be
complex, so I want to avoid to use two queries.
I had hoped that there would be a way to do just ask one query with
the
LIMIT keyword and also get the total number of lines.
In that case, I'd probably just do the query once, without the LIMIT,
but throw away the results after you've reached 1000, just count the
number of rows. In C++ this seems to be pretty quick, but I'm not sure
what it would be like if the client code was written in TCL.
Don't know about Tcl, but make sure that you don't fetch all the
records (analogy: fetchall... methods in Perl DBI), but fetch just a
pointer to the cursor (fetchrow... methods), and then step through the
cursor. If you fetch all the records then the whole point is defeated.
An alternative method is to define a separate table that keeps the
COUNT of the rows, and define a trigger that keeps that COUNT updated
every time you DELETE/INSERT/UPDATE on the main table.
All depends on what you mean by "database is very large." For most
"large" SQLite still should be very fast, but if its largeness is
indeed contributing to a slowdown then the above alternatives should
work.
Johan
>Bert Verhees wrote:
>IMHO COUNT does a complete tablescan to count the records, it did in
a
>previous version of sqlite
>Bert
>
>> Paolo Vernazza wrote:
>> I'm not sure what do you need... but you tried using
>> SELECT COUNT(*) FROM etc etc etc
>> Paolo
>>
>>> Trygg Johan wrote:
>>>
>>> Hello,
>>>
>>> I'm using SQLite with TCL and I have a small question:
>>>
>>> Is it possible to get information on how many lines a select query
with
>>> the LIMIT keyword would have produced if I hadn't used the LIMIT
>>> keyword?
>>>
>>> One way of doing this would be to do another query without the
LIMIT
>>> keyword, count the number of lines you got, and then throw away
the
>>> resulting data, but this seem to be a bit of waste of both
resources
and
>>> time... so I hope someone has a better solution :)
>>>
>>> Thanks in advance,
>>> Johan Trygg
>>>
Paul VPOP3 - Internet Email Server/Gateway
[EMAIL PROTECTED] http://www.pscs.co.uk/
--
Puneet Kishor
