COUNT(*) very slowly in large InnoDB table
Hi!
I am quite new to handling large tables, so maybe you can give me a hint on this one:
I run a very simple query on a InnoDB table with about 4.500.000 entries. There is an index on the ID and two further UNIQUE-indices.
SELECT COUNT(*) FROM `mytable`
SELECT COUNT(id) FROM `mytable`
Both querys takes about 12 seconds to run (accompanied by high CPU usage). OPTIMIZE TABLE did take even longer (and was a very bad idea to do, because the server was down for an hour), but did not significantly reduce the query time to 8 seconds. I guess there is something wrong, because a much more complex query takes approx. the same time:
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)
Although I would understand that counting all these stuff takes some time, I wonder why the COUNT(*) takes so long.
Thank you for hints - and if anyone has an idea how to speed up the second query, I would apprechiate that, too, of course :) However it is important, that there are much more INSERTs to the table than this query. So a index on "started" seems not useful to me?!
Greetings
Dominik
CREATE TABLE `mytable` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`project` INT(11) NOT NULL,
`token` VARCHAR(64) NULL DEFAULT NULL COLLATE 'utf8_bin',
`num` INT(10) UNSIGNED NOT NULL,
`serial` VARCHAR(64) NULL DEFAULT NULL COLLATE 'utf8_bin',
`reference` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_bin',
`questionnaire` VARCHAR(64) NULL DEFAULT NULL COLLATE 'utf8_bin',
`started` INT(11) NULL DEFAULT NULL,
`mode` ENUM('interview','admin','debug','pretest','orgtest') NULL DEFAULT NULL COLLATE 'utf8_bin',
`last_page` SMALLINT(6) NOT NULL DEFAULT '0',
`finished` ENUM('no','yes') NOT NULL DEFAULT 'no' COLLATE 'utf8_bin',
`results` TEXT NULL COLLATE 'utf8_bin',
`times` TEXT NULL COLLATE 'utf8_bin',
`pageHistory` TEXT NULL COLLATE 'utf8_bin',
`numHistory` TEXT NULL COLLATE 'utf8_bin',
`server_info` TEXT NULL COLLATE 'utf8_bin',
`annotations` TEXT NULL COLLATE 'utf8_bin',
`variablesQuestionnaire` TEXT NULL COLLATE 'utf8_bin',
`variablesSystem` TEXT NULL COLLATE 'utf8_bin',
`currentPage` TEXT NULL COLLATE 'utf8_bin',
PRIMARY KEY (`id`),
UNIQUE INDEX `num` (`project`, `num`),
UNIQUE INDEX `project` (`project`, `token`),
CONSTRAINT `projectCaseC` FOREIGN KEY (`project`) REFERENCES `ofb_projects` (`id`) ON DELETE CASCADE
)
COLLATE='utf8_bin'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=4641713
Edited 2 time(s). Last edit at 07/13/2010 12:02PM by Dominik Leiner.