List:General Discussion« Previous MessageNext Message »
From:Jeremy Zawodny Date:November 7 2002 9:26pm
Subject:Re: Key/table cache size?
View as plain text  
On Thu, Nov 07, 2002 at 12:32:58PM -0500, Michael T. Babcock wrote:
> 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?

Check if the machine is disk or CPU bound when it runs.  You may need
a larger innodb_buffer_pool.  That'll help keep more records in memory
and imporove response time.

> 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?

Maybe.  Depends what else it might be used for.

Jeremy
-- 
Jeremy D. Zawodny     |  Perl, Web, MySQL, Linux Magazine, Yahoo!
<Jeremy@stripped>  |  http://jeremy.zawodny.com/

MySQL 3.23.51: up 93 days, processed 1,954,140,347 queries (241/sec. avg)
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