SQLite3 and lastrowid

[Alexander Gattin]

Hello,

On Fri, Nov 19, 2010 at 01:03:14PM +0100, Wolfgang
Rohdewald wrote:
> On Freitag 19 November 2010, Alexander Gattin wrote:
> > It's better to select count(1) instead of
> > count(*).

not true,

> > The latter may skip rows consisting
> > entirely of NULLs IIRC.

not true either. I've heard that count(1) is
preferred to count(*) but forgot why. Your post
reveals the truth about my belief:

> in some data bases count(1) is said to be faster
> than count(*), I believe

And as it turns out this isn't true anymore on
modern databases (count(1) and count(*) behave
exactly the same). And quite surprisingly, on old
ones count(*) was faster than count(1):

> 'Oracle Performance Tuning', second edition,
> O'Reilly & Associates, Inc, page 175.  It says:
> 
> "Contrary to popular belief COUNT(*) is faster
> than COUNT(1).  If the rows are returned via an
> index, counting the indexed column - for example,
> COUNT(EMP_NO) is faster still.  The optimizer
> realizes from the existence of the index that the
> column must also exist (non-null).  We tested the
> following statements on several different
> computers and found that COUNT(*) consistently
> runs between 15% and 20% faster than COUNT(1) and
> that COUNT(INDEXED_COLUMN) is 5% faster again."

// http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1156151916789

P.S. sorry for starting this discussion.

-- 
With best regards,
xrgtn