To quote from a note on the MYSQL 5.0 manual:
"Posted by Galt Barber on August 26 2006 9:39am
Select count(distinct somefield) from sometable is very slow in all versions of mysql.
The distinct part is equivalent to:
select somefield from sometable group by somefield
although the latter sometimes runs even faster than distinct. To get reasonable speed under mysql5, please use this equivalent which uses a subquery:
select count(*) from (select distinct somefield from sometable group by somefield) as somelabel;
This is quite speedy.
(I don't know why count distinct is so slow when distinct itself isn't that slow.)"
It would probably be better if the PHP scripts in the next version of BASE were re-written in this form to accommodate an admitted bug in the MYSQL code base. The MYSQL developers aren't scheduled to fix it until sometime in version 5.2--which is a long way from stability.
Alternatively, have others had better luck using other, free databases with the BASE scripts?