Another Slow Query/ Indexing Problem 1

[Tama]

Hi there

I've been taking the advice that vanekl gave me in and been identifying indexing issues by first indentifying slow queries on my website, and then running them through EXPLAIN.

This has been working very well (big props to vanekl) but I've run into something that I can't work out by myself.

This is an example of the slow query:

SELECT t.forum_id, t.topic_id, p.post_time
FROM nuke_bbtopics t, nuke_bbposts p
WHERE p.post_id = t.topic_last_post_id AND p.post_time >1075773804 AND t.topic_moved_id =0

This is the structure of the tables involved

CREATE TABLE `nuke_bbposts` (
  `post_id` mediumint(8) unsigned NOT NULL auto_increment,
  `topic_id` mediumint(8) unsigned NOT NULL default '0',
  `forum_id` smallint(5) unsigned NOT NULL default '0',
  `poster_id` mediumint(8) NOT NULL default '0',
  `post_time` int(11) NOT NULL default '0',
  `poster_ip` varchar(8) NOT NULL default '',
  `post_username` varchar(25) default NULL,
  `enable_bbcode` tinyint(1) NOT NULL default '1',
  `enable_html` tinyint(1) NOT NULL default '0',
  `enable_smilies` tinyint(1) NOT NULL default '1',
  `enable_sig` tinyint(1) NOT NULL default '1',
  `post_edit_time` int(11) default NULL,
  `post_edit_count` smallint(5) unsigned NOT NULL default '0',
  `post_attachment` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`post_id`),
  KEY `forum_id` (`forum_id`),
  KEY `topic_id` (`topic_id`),
  KEY `poster_id` (`poster_id`),
  KEY `post_time` (`post_time`)
) TYPE=MyISAM;

CREATE TABLE `nuke_bbtopics` (
  `topic_id` mediumint(8) unsigned NOT NULL auto_increment,
  `forum_id` smallint(8) unsigned NOT NULL default '0',
  `topic_title` char(60) NOT NULL default '',
  `topic_poster` mediumint(8) NOT NULL default '0',
  `topic_time` int(11) NOT NULL default '0',
  `topic_views` mediumint(8) unsigned NOT NULL default '0',
  `topic_replies` mediumint(8) unsigned NOT NULL default '0',
  `topic_status` tinyint(3) NOT NULL default '0',
  `topic_vote` tinyint(1) NOT NULL default '0',
  `topic_type` tinyint(3) NOT NULL default '0',
  `topic_last_post_id` mediumint(8) unsigned NOT NULL default '0',
  `topic_first_post_id` mediumint(8) unsigned NOT NULL default '0',
  `topic_moved_id` mediumint(8) unsigned NOT NULL default '0',
  `topic_attachment` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`topic_id`),
  KEY `forum_id` (`forum_id`),
  KEY `topic_moved_id` (`topic_moved_id`),
  KEY `topic_status` (`topic_status`),
  KEY `topic_type` (`topic_type`)
) TYPE=MyISAM;

If I run the query through the EXPLAIN command I get the following results:

table  type  possible_keys      key     key_len  ref                  rows  Extra  
t      ALL   topic_moved_id     NULL    NULL     NULL                 8561  where used 
p      eq_ref PRIMARY,post_time PRIMARY 3        t.topic_last_post_id 1     where used

I'm confused why it is searching all 8561 rows when topic_moved_id is an index.

If anyone could shed some light on this problem it would be greatly appreciated.

Cheers
Tama

 

[KarveR]

because it is checking every record to find what fits the criteria - none of the where clauses are definitive.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[vanekl]

There is a definitive WHERE clause: t.topic_moved_id =0
MySQL will key in on this clause because it is a constant.
The only problem is, I'm betting there are tons of 0's in this column,
so the index is being ignored (see below).


Have you done an 'ANALYZE TABLE nuke_bbposts, nuke_bbtopics' recently?


What do you get when you do,

SELECT COUNT(*) FROM nuke_bbtopics t
WHERE t.topic_moved_id =0;

Is this count > 30% of the table? If so, the topic_moved_id index will be ignored.
I guess you could also do a
SHOW INDEX FROM nuke_bbtopics
and check the cardinality of your indexes. If the cardinality is small (<5)
then the index will probably be ignored.


I'm guessing the problem is you don't have an index on the `topic_last_post_id` field.
If this field is unique you should add the word UNIQUE, too:

UNIQUE INDEX `topic_last_post_id_idx` (`topic_last_post_id`),

 

[Tama]

Thanks guys - I'd done some more searching after my original post and have begun to understand about the relationship between cardinality and the use of indexes. I hadn't had the 30% rule spelt out to me though - thank you for that.

Since the cardinality of my 8500 &quot;topic_moved_id&quot; fields was 5(!) there was no way that MySQL would use the index.

I've since rewritten the query/code removing the &quot;topic_moved_id&quot; bit completely. Hopefully this will work.

Cheers
Tama

 

[herman75]

Seems to me you'd want an index on post_time if this query is slow.

To be sure create the index and try a query with EXPLAIN to see wether the index is being used though.

Goodluck