MySQL Forums
Forum List  »  Performance

COUNT(*) very slowly in large InnoDB table
Posted by: Dominik Leiner
Date: July 13, 2010 11:43AM

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.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.