Re: CPU usage 99% - and query very slow - help

[Rick James]

High CPU --> need to find worst query/queries --> add index and/or redesign query

You have the slow log; find the worst

To assist in analyzing slow SELECTs, please provide
* SHOW CREATE TABLE tbl\G -- engine, indexes
* SHOW TABLE STATUS LIKE 'tbl'\G -- sizes
* EXPLAIN SELECT ...\G -- clues of inefficiencies
* SHOW VARIABLES LIKE '%buffer%'; -- cache size
and surround them with [ code ] and [ / code ]
How much RAM do you have?

> all tables have indexes
Need the details.

> and are optimizaed every 3 days.
Usually a waste to do it that often. Less than 1% of tables need periodic OPTIMIZE. Monthly is probably often enough. (Again, need details to comment further.)