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)
