On 12/13/06, [EMAIL PROTECTED]
<[EMAIL PROTECTED]> wrote:
"Scott Hess" <[EMAIL PROTECTED]> writes:
> On 12/13/06, RB Smissaert <[EMAIL PROTECTED]> wrote:
>> Trying to find the fastest way to determine if a table has no rows.
>>
>> I think this will do:
>> SELECT (SELECT ROWID FROM table limit 1) IS NOT NULL;
>> If a table has rows then the result should be 1.
>
> What's wrong with "SELECT COUNT(*) FROM table;"?
COUNT(*) is generally slow. It actually iterates over every row, so with
large tables, it's very slow. By using a query with a "LIMIT 1", you ensure
that as soon as a single matching row is found, no additional rows are
searched for.
Hmm, that's a good point, I was thinking in terms of the empty case.
Indeed, in the past I've used one of:
SELECT max(rowid) FROM table;
SELECT rowid FROM table ORDER BY rowid DESC LIMIT 1;
as a proxy for COUNT(*). Obviously this only works for a table which
hasn't seen deletions (or who's deletions have only been at the end
and you created the table appropriately). I don't remember why both
versions stick in my mind, maybe I tried both and just don't remember
which was faster. Also, this returns null if the table is empty, so
you might want:
SELECT ifnull(max(rowid),0) FROM table;
but, honestly, if you're this worried about speed you should manage
the null test in your C code.
-scott
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
