List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 7 1999 4:11pm
Subject:Re: COUNT(*) problem... way too slow!
View as plain text  
On Tue, 1999-09-07 12:04:44 -0700, David Stack wrote:
> select count(*) from items where show_item=1
[...]
> >The table is about 330,000 rows and show_item is the value 1,2, or
> >3.  show_item is not indexed and show_item is a TINYINT.
> >
> >When I run the query it runs and runs and never seems to finish
> >running (even after 10 minutes).  Something is definity wrong, but
> >I don't know what?

Two notes:
- adding a index on show_item will give you a very big speed boost!
  That's what indexes are really made for ... :)

- I can't remember clearly, but I think I read something about a bug
  with TINYINT columns.  Sorry can't be more specific here,

If indexing doesn't help (but I'm sure it will) you might try if
changing the column type helps.  For cases like this one with only a
few possible values, I'd suggest using ENUM('1','2','3').  Then don't
forget to change comparisons to use the string notion: where
show_item='1'

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
COUNT(*) problem... way too slow!David Stack7 Sep
  • Re: COUNT(*) problem... way too slow!Martin Ramsch7 Sep
RE: COUNT(*) problem... way too slow!Juan Manuel Doren7 Sep