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
[EMAIL PROTECTED]
---------------------------------------------------------------------
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
