Re: how to configure my.cnf for mysql 5.1, using too much cpu?

[Rick James]

Tune your way out of a CPU problem? Unlikely.

There are, however, some things that should be tuned. See
http://mysql.rjweb.org/doc.php/memory
for cache settings. Aside from it's advice, I suggest setting
long_query_time = 2
query_cache_type = OFF

Are you using only MyISAM?

SELECT COUNT(*) FROM HugeTable WHERE ...;
will take time. To start with, it will take time to fetch the data from disk. But, even it it is cached, it may take a lot of CPU to look at the records to do the filtering.

Please provide SHOW CREATE TABLE and EXPLAIN SELECT for the slow queries.

Please provide SHOW GLOBAL STATUS; for more analysis.

If this is a "Data Warehouse" application, there are more suggestions to provide.