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! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 3.23.51: up 93 days, processed 1,954,140,347 queries (241/sec. avg) --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
