MySQL Forums
Forum List  »  Optimizer & Parser

Re: avoid filesort
Posted by: Magnus Larsson
Date: January 24, 2012 04:36PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
1578
January 22, 2012 01:08PM
948
January 24, 2012 01:17AM
Re: avoid filesort
1049
January 24, 2012 04:36PM
1153
January 25, 2012 07:24PM
998
January 26, 2012 04:57AM
985
January 25, 2012 08:26PM
965
January 26, 2012 04:56AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.