Re: any faster select count(*)

[Rick James]

Some of what some of you said is sometimes correct.

SELECT COUNT(*) FROM MyISAM_table; -- Instantaneous
SELECT COUNT(1) FROM MyISAM_table; -- Instantaneous
The size of a MyISAM table is dead-reckoned.

SELECT COUNT(...) FROM InnoDB_table; -- Not instantaneous
InnoDB must be transaction-safe.

(Notation: By "...", I mean anything can be here.)

SELECT COUNT(*) FROM ... WHERE ...; -- Not Instantaneous
Something has to be scanned and filtered (by the WHERE clause) to count the rows.

SELECT COUNT(col) ... -- Must check whether col is NULL.
(I do not know whether it looks at the declaration to take a short cut.)

SELECT COUNT(...) FROM TABLE WHERE indexed_col = 'constant'
This will use the index, but it will still have to scan through that index from the first occurrence of 'constant' to the last. This is usually noticeably faster than if it has to read the "data". Phrased another way: A "table scan" is usually slower than an "index scan".

Note: the "data" and each "index" are stored separately, and the data is usually bulkier. Exeception: In InnoDB, the PRIMARY KEY is stored with the data.

SELECT COUNT(...) FROM TABLE WHERE indexed_col BETWEEN 234 AND 567
Essentially the same as above.
Ditto for various flavors of inequality tests with constants.

SELECT COUNT(...) FROM TABLE WHERE non_indexed_col = 'constant'
Now it has to scan the entire table. Not fast.

INDEX(a,b)
SELECT COUNT(...) FROM TABLE WHERE b = 'constant'
Now it has to scan the entire index. Not fast, but faster than a table scan. To be efficient, `b` needed to be the _first_ column in the "compound" index.

Note that EXPLAIN EXTENDED will turn COUNT(*) into COUNT(0), implying that they are identical, and that probably COUNT(0) is best. (I use COUNT(*) anyway.)

To test for existence, use EXISTS:
SELECT ... WHERE EXISTS (SELECT * FROM ... WHERE ...)
This is probably better than the LIMIT 1 trick. Note: "*" does not really fetch all the columns.

While we are talking in this area...
SELECT SUM(x='abc') FROM ...
Will count the number of rows with x='abc'
This is because (x='abc') is a boolean; booleans are TRUE or FALSE, which are aliases for 1 and 0. So, that is the SUM of some 1's and 0's.

irek, was your xyz table MyISAM or InnoDB?

I, too, would not take much stock in a 2.5% difference.

If I have failed to cover all the nuances, please ask.