MySQL Forums :: Performance :: COUNT(*) very slowly in large InnoDB table


Advanced Search

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


Subject Views Written By Posted
COUNT(*) very slowly in large InnoDB table 11656 Dominik Leiner 07/13/2010 11:43AM
Re: COUNT(*) very slowly in large InnoDB table 4076 Rick James 07/14/2010 11:04PM
Re: COUNT(*) very slowly in large InnoDB table 3867 Dominik Leiner 07/15/2010 02:24AM
Re: COUNT(*) very slowly in large InnoDB table 4200 Rick James 07/15/2010 08:51AM
Re: COUNT(*) very slowly in large InnoDB table 2276 Dominik Leiner 07/15/2010 09:48AM
Re: COUNT(*) very slowly in large InnoDB table 2324 Rick James 07/15/2010 09:09PM
Re: COUNT(*) very slowly in large InnoDB table 1930 Dominik Leiner 07/16/2010 12:58AM
Re: COUNT(*) very slowly in large InnoDB table 2759 abilash vs 07/19/2010 12:18PM
Re: COUNT(*) very slowly in large InnoDB table 2677 Dominik Leiner 07/19/2010 12:45PM
Re: COUNT(*) very slowly in large InnoDB table 2035 abilash vs 07/19/2010 09:25PM
Re: COUNT(*) very slowly in large InnoDB table 1915 wayne r 07/20/2010 10:38PM
Re: COUNT(*) very slowly in large InnoDB table 1543 abilash vs 07/21/2010 11:52AM
Re: COUNT(*) very slowly in large InnoDB table 2575 Rick James 07/21/2010 12:11PM
Re: COUNT(*) very slowly in large InnoDB table 2409 Wim De Saegher 07/22/2010 03:23AM


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.