Re: COUNT(*) very slowly in large InnoDB table

[Rick James]

ACID considerations require InnoDB to scan at least an index to do COUNT(*).

Your query:

SELECT  COUNT(*) AS cnAll,
        SUM(mode="interview") AS interviews,
        SUM((finished="yes") AND  (mode="interview")) AS complete,
        SUM((mode="admin") OR  (mode="debug")) AS development,
        SUM((mode="pretest") OR  (mode="orgtest")) AS pretest
    FROM  `mytable`
    WHERE  (started > 1278954091)
      AND  (started <= 1279040491)

Suggestions:

* INDEX(started) is likely to help

* INDEX(started, mode, finished) would have the EXPLAIN saying "Using index", which is likely to help some.

* innodb_buffer_pool_size should be about 70% of _available_ RAM.

* Consider doing SUM(mode IN ("pretest", "orgtest")) (won't help with speed)

* Consider "vertical partitioning". You have about 9 TEXT fields. How bulky are they? (SHOW TABLE STATUS LIKE 'mytable';) Use the same id and have PRIMARY KEY(id), but not AUTO_INCREMENT, in the new table. (This may or may not help this and/or other queries.)

* Why utf8_bin?