Ok, this almost certainly has to do with GROUPing on a prefix. Ok, one suggestion would be upgrading to MySQL 4.1, the optimizer gets smarter every version, I can't guarentee an improvement but you never know. You might try increasing the sort_buffer setting to allow for more memory for the sort, and you might try making temporary tables. Query all the items for site ID 5 into a table, stripped to prefixes, then group by them and do the count.
Mike Hillyer, Technical Writer
MySQL AB, www.mysql.com
Office: +1 403-380-6535
Blog:
http://www.openwin.org/mike
"The Open Source movement has become a major force across the software industry, and MySQL is the world's most popular open source database."
--Fortune Magazine