Dear Gentlemen, I have a performance problem with one of the tables of a given DB schema. The schema is not allowed to be modified (except adding index or changing db engines) because it is used by a third party SW which I cannot modify. The schema looks like this: +-------------------+--------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------------+--------------+------+-----+-------------------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | create_time | int(11) | NO | | | | | workunitid | int(11) | NO | MUL | | | | server_state | int(11) | NO | MUL | | | | outcome | int(11) | NO | | | | | client_state | int(11) | NO | | | | | hostid | int(11) | NO | MUL | | | | userid | int(11) | NO | MUL | | | | report_deadline | int(11) | NO | | | | | sent_time | int(11) | NO | | | | | received_time | int(11) | NO | MUL | | | | name | varchar(254) | NO | UNI | | | | cpu_time | double | NO | | | | | xml_doc_in | blob | YES | | NULL | | | xml_doc_out | blob | YES | | NULL | | | stderr_out | blob | YES | | NULL | | | batch | int(11) | NO | | | | | file_delete_state | int(11) | NO | MUL | | | | validate_state | int(11) | NO | | | | | claimed_credit | double | NO | | | | | granted_credit | double | NO | | | | | opaque | double | NO | | | | | random | int(11) | NO | | | | | app_version_num | int(11) | NO | | | | | appid | int(11) | NO | MUL | | | | exit_status | int(11) | NO | MUL | | | | teamid | int(11) | NO | | | | | priority | int(11) | NO | | | | | mod_time | timestamp | NO | | CURRENT_TIMESTAMP | | +-------------------+--------------+------+-----+-------------------+----------------+ 29 rows in set (0.07 sec) mysql> show index from result; +--------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+ | result | 0 | PRIMARY | 1 | id | A | 172984 | NULL | NULL | | BTREE | | | result | 0 | name | 1 | name | A | 172984 | NULL | NULL | | BTREE | | | result | 1 | res_wuid | 1 | workunitid | A | 172984 | NULL | NULL | | BTREE | | | result | 1 | ind_res_st | 1 | server_state | A | 3 | NULL | NULL | | BTREE | | | result | 1 | ind_res_st | 2 | priority | A | 15 | NULL | NULL | | BTREE | | | result | 1 | res_app_state | 1 | appid | A | 1 | NULL | NULL | | BTREE | | | result | 1 | res_app_state | 2 | server_state | A | 3 | NULL | NULL | | BTREE | | | result | 1 | res_filedel | 1 | file_delete_state | A | 2 | NULL | NULL | | BTREE | | | result | 1 | res_userid_id | 1 | userid | A | 1383 | NULL | NULL | | BTREE | | | result | 1 | res_userid_id | 2 | id | A | 172984 | NULL | NULL | | BTREE | | | result | 1 | res_userid_val | 1 | userid | A | 1383 | NULL | NULL | | BTREE | | | result | 1 | res_userid_val | 2 | validate_state | A | 2084 | NULL | NULL | | BTREE | | | result | 1 | res_hostid_id | 1 | hostid | A | 2507 | NULL | NULL | | BTREE | | | result | 1 | res_hostid_id | 2 | id | A | 172984 | NULL | NULL | | BTREE | | | result | 1 | res_wu_user | 1 | workunitid | A | 172984 | NULL | NULL | | BTREE | | | result | 1 | res_wu_user | 2 | userid | A | 172984 | NULL | NULL | | BTREE | | | result | 1 | idx_received_time | 1 | received_time | A | 10175 | NULL | NULL | | BTREE | | | result | 1 | idx_exit_status | 1 | exit_status | A | 13 | NULL | NULL | | BTREE | | +--------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+ 18 rows in set (0.00 sec) Some more statistics about the particular table: mysql> SELECT COUNT(*) FROM result; +----------+ | COUNT(*) | +----------+ | 172984 | +----------+ 1 row in set (0.00 sec) The following queries take quite long: SELECT received_time as raw_date, DATE_FORMAT(FROM_UNIXTIME(received_time), '%d. %M %Y') AS format_date, count(case when server_state=5 and outcome=1 then 1 end) as wu_success, sum(cpu_time/3600) as cpu_hours, count(case when server_state=5 and outcome!=1 and outcome!=4 then 1 end) as wu_other FROM result GROUP BY format_date DESC ORDER BY raw_date DESC +----+-------------+--------+------+---------------+------+---------+------+--------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+--------+---------------------------------+ | 1 | SIMPLE | result | ALL | NULL | NULL | NULL | NULL | 172984 | Using temporary; Using filesort | +----+-------------+--------+------+---------------+------+---------+------+--------+---------------------------------+ and the second query: SELECT count(case when server_state=4 then 1 end) as wu_progress, count(case when server_state<4 then 1 end) as wu_unsent FROM result WHERE server_state<5 and received_time=0 +----+-------------+--------+------+------------------------------+-------------------+---------+-------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+------------------------------+-------------------+---------+-------+--------+-------------+ | 1 | SIMPLE | result | ref | ind_res_st,idx_received_time | idx_received_time | 4 | const | 148234 | Using where | +----+-------------+--------+------+------------------------------+-------------------+---------+-------+--------+-------------+ They both take around 30seconds which is quite a lot for generating realtime statistics :-) Any ideas how to tune them? Mysql: Server version: 5.0.30 Gentoo Linux mysql-5.0.30 using myisam tables (no innodb) key_buffer_size = 240M The box is a Pentium4/HT 2.4 GHZ (CPU is only at 6% when running the queries) with 512MB RAM. TIA, Thomas