speed up this query ( join + mediumtext field)

Question

I have this query

SELECT 
        t.name,t.userid,t.date,t.cat_id,t.forum_id,t.reply,t.hidden,t.moderated,t.sticky,t.statut,t.poll,t.helpful,t.del,
        t_data.message,
        user.name AS author_name,user.level AS author_level,user.is_globalMod AS author_global,user.award,<br>
        user.statut AS author_statut,user.posts AS user_posts,user.point AS user_points,user.title AS user_title,
        user.image AS user_avatar,user.sex AS user_sex,user.other_level,user.hid_posts
        FROM frm_thread AS t
        LEFT JOIN frm_thread_data AS t_data ON t_data.thread_id = t.id
        LEFT JOIN frm_member AS user ON user.id =t.userid
        WHERE t.id = 248925

frm_thread is

CREATE TABLE IF NOT EXISTS `frm_thread` (
  `id` mediumint(7) unsigned NOT NULL auto_increment,
  `last_userid` int(10) unsigned NOT NULL,
  `last_date` int(10) unsigned default NULL,
  `forum_id` tinyint(5) unsigned NOT NULL,
  `cat_id` tinyint(3) unsigned NOT NULL,
  `name` varchar(250) collate utf8_unicode_ci NOT NULL,
  `userid` int(10) unsigned NOT NULL,
  `date` int(10) unsigned default NULL,
  `view` mediumint(5) unsigned NOT NULL,
  `reply` smallint(4) NOT NULL,
  `sticky` tinyint(1) unsigned NOT NULL,
  `linked` tinyint(1) unsigned NOT NULL,
  `linked_order` tinyint(2) unsigned NOT NULL,
  `spam` tinyint(1) unsigned NOT NULL,
  `statut` tinyint(1) unsigned NOT NULL,
  `moderated` tinyint(1) unsigned NOT NULL,
  `hidden` tinyint(1) unsigned NOT NULL,
  `poll` smallint(5) unsigned NOT NULL,
  `del` tinyint(1) unsigned NOT NULL,
  `t_icon` smallint(10) unsigned NOT NULL,
  `helpful` int(10) unsigned NOT NULL
  PRIMARY KEY  (`id`),
  KEY `forum_id` (`forum_id`),
  KEY `last_date` (`last_date`,`date`,`sticky`),
  KEY `linked` (`linked`),
  KEY `date` (`date`),
  KEY `userid` (`userid`),
  KEY `last_userid` (`last_userid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=249014 ;

frm_thread_data

CREATE TABLE IF NOT EXISTS `frm_thread_data` (
  `thread_id` int(10) unsigned NOT NULL,
  `message` mediumtext collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`thread_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

as you can see, the message content is in a separated table, now this query normaly is completed in 0.0005 which is shown by codeigniter profiling

bu somethimes it takes up to 25.0050 sec

i have a posts table but the text field is not in a seperated table and it always gives a number close to 0.0020

is there a way to imporve this query please ?

EXPLAIN SHOW's

id  select_type     table   type    possible_keys   key         key_len     ref     rows Extra
1   SIMPLE          t       const   PRIMARY         PRIMARY     3           const   1    
1   SIMPLE          t_data  const   PRIMARY         PRIMARY     4           const   1    
1   SIMPLE          user    const   PRIMARY         PRIMARY     4           const   1

Maurizio Cucchiara Maurizio Cucchiara · Accepted Answer · 2010-11-08T18:13:06

I guess that time oscillation it's cache related.

Try to use query profiler Use explain directive for further details

Take a look at top 10 SQL Performance tips

speed up this query ( join + mediumtext field)

1 Answers