List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:November 7 2002 8:07pm
Subject:Re: Key/table cache size?
View as plain text  
In the last episode (Nov 07), Michael T. Babcock said:
> I've got a query that selects the count of distinct rows in a table;
> the table has 453632 rows and 24 distinct class_id's.
> 
> mysql> SELECT count(DISTINCT(sig_class_id)) FROM acid_event;
> | count(DISTINCT(sig_class_id)) |    24 |
> 1 row in set (4.80 sec)
> 
> mysql> select count(*) FROM acid_event;
> | count(*) |   453632 |
> 1 row in set (3.12 sec)
> 
> Is there any way to speed up these queries (InnoDB tables), or just
> put up?
> 
> PS, at that number of distinct values, am I right in thinking that I
> should just drop the index on that column while I'm at it?

I haven't played with InnoDB tables much, but a select like that runs
much faster with an index on MyISAM tables.  Most of the server's time
should be spent reading the field values, and an index scan of just
that field will be faster than a table scan where it has to read the
whole record.

Yes, with only 24 values, MySQL will probably never use that index for
paring down records in a regular SELECT.  But for your count() case it
should.

-- 
	Dan Nelson
	dnelson@stripped
Thread
Key/table cache size?Michael T. Babcock7 Nov
  • Re: Key/table cache size?Dan Nelson7 Nov
  • Re: Key/table cache size?Michael T. Babcock7 Nov
  • Re: Key/table cache size?Jeremy Zawodny7 Nov
RE: Key/table cache size?Peter Grigor7 Nov
  • Re: Key/table cache size?Michael T. Babcock7 Nov