Thank you for your response!
I really appreciate the help.
Ok, i think i understand what you are saying regarding the filesort. Today the query takes about 0.3 seconds and that is kinda annoying due to that this query is executed on every page you land on.
Big thanks again for you help so far.
All the information you asked for is shown below.
* SHOW CREATE TABLE tbl\G -- engine, indexes
CREATE TABLE `f_post` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`founder` int(11) NOT NULL DEFAULT '0',
`postdate` int(11) NOT NULL,
`text` text NOT NULL,
`user_id` int(11) NOT NULL,
`edit_last` int(11) NOT NULL,
`edit_count` int(11) NOT NULL,
`thread_id` int(11) NOT NULL,
`category` int(11) NOT NULL,
`cens` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `trad_id` (`trad_id`),
KEY `user_id` (`user_id`),
KEY `cat` (`cat`)
) ENGINE=MyISAM AUTO_INCREMENT=80751 DEFAULT CHARSET=latin1
Name f_post
Engine MyISAM
Version 10
Row_format Dynamic
Rows 102605
Avg_row_length 134
Data_length 13850136
Max_data_length 281474976710655
Index_length 4715520
Data_free 0
Auto_increment 102909
Create_time 2011-11-24 09:16:44
Update_time 2012-01-24 23:12:00
Check_time 2011-11-24 09:19:02
Collation latin1_swedish_ci
Checksum NULL
Create_options
Comment
* EXPLAIN SELECT ...\G -- clues of inefficiencies
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY f_thread ALL NULL NULL NULL NULL 12237 Using where; Using filesort
2 DEPENDENT SUBQUERY f_post ref thread_id thread_id 4 table.f_thread.id 8
* SHOW VARIABLES LIKE '%buffer%'; -- cache size
bulk_insert_buffer_size 8388608
innodb_buffer_pool_size 8388608
innodb_log_buffer_size 1048576
join_buffer_size 37748736
key_buffer_size 2415919104
myisam_sort_buffer_size 1207959552
net_buffer_length 16384
preload_buffer_size 32768
read_buffer_size 37748736
read_rnd_buffer_size 28311552
sort_buffer_size 37748736
sql_buffer_result OFF