CODEWITHSUNDEEP
mysqlmyisam
Fluffy
Fluffy

Reputation: 28382

Is there any way to do a fast inaccurate count(*) with a where condition in myisam?

I have a table with 150 mln rows with such a definition:

CREATE TABLE `tasks` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `task` varchar(255) NOT NULL,
  `done` tinyint unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `done` (`done`)
) ENGINE=MyISAM AUTO_INCREMENT=154505834 DEFAULT CHARSET=utf8

select count(*) from tasks where done = 1; takes 20 seconds.

I don't care about the exact value too much: no problem to have it rounded to the nearest million. Is there a way to make the less query less accurate, but faster?

Upvotes: 3

Views: 55

Answers (1)

Brian
Brian

Reputation: 6450

If you've tried indexing and it can't be sped up, and upgrading hardware (RAM, SSD, whatever) isn't an option, consider some form of "architecture" approach: e.g. record a nightly snapshot of the count that you can lookup throughout the next day? Is this accurate enough?

Edit: or, hourly, or whatever frequency works!

Upvotes: 1

Related Questions