[SOLVED] Index Not Getting Used

[neoform]

I'm making a messaging system, but have run into a problem with one of my queries not using the proper index. I keep getting a "ALL" for the query. If I don't have the right index, what should it be?

 

SELECT
bodies.posted_on,	
bodies.user_id,		
recipients.thread_id id,
SUM(IF(bodies.id > recipients.viewed_body_id, 1, 0)) new_messages
FROM			
system_messages_recipients recipients
INNER JOIN
system_messages_bodies bodies
ON
recipients.thread_id = bodies.thread_id
WHERE							
recipients.recipient_user_id = '1'
AND
bodies.id > recipients.delete_body_id

 

+----+-------------+------------+--------+-------------------+---------+---------+-----------------------------------+--------+-------------+
| id | select_type | table      | type   | possible_keys     | key     | key_len | ref                               | rows   | Extra       |
+----+-------------+------------+--------+-------------------+---------+---------+-----------------------------------+--------+-------------+
|  1 | SIMPLE      | bodies     | ALL    | PRIMARY,posted_on | NULL    | NULL    | NULL                              | 400232 |             |
|  1 | SIMPLE      | recipients | eq_ref | PRIMARY           | PRIMARY | 7       | phpneoform.bodies.thread_id,const |      1 | Using where |
+----+-------------+------------+--------+-------------------+---------+---------+-----------------------------------+--------+-------------+

 

-- Table "system_messages_bodies" DDL

CREATE TABLE `system_messages_bodies` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `thread_id` int(10) unsigned NOT NULL,
  `posted_on` datetime NOT NULL,
  `user_id` mediumint( unsigned NOT NULL,
  `body` varchar(60000) NOT NULL,
  `body_hash` binary(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `hash` (`body_hash`),
  KEY `posted_on` (`id`,`thread_id`,`posted_on`)
) ENGINE=MyISAM AUTO_INCREMENT=400233 DEFAULT CHARSET=latin1;

 

-- Table "system_messages_recipients" DDL

CREATE TABLE `system_messages_recipients` (
  `thread_id` int(10) unsigned NOT NULL,
  `recipient_user_id` mediumint( unsigned NOT NULL,
  `viewed_body_id` int(10) unsigned NOT NULL,
  `delete_body_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`thread_id`,`recipient_user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

[Mchl]

Try creating index on recipient_user_id

[obsidian]

MySql is notorious (possibly too strong a word) for not always using the index that you would want, if it thinks there is a more optimal one to use. If you know the name of the index you wish to use on a specific query, you can always use FORCE INDEX to specify the one you want.

 

http://dev.mysql.com/doc/refman/5.1/en/index-hints.html

[neoform]

I actually did that. I got the same result. Which leads me to think that maybe I'm not seeing how the index is supposed to be designed..

[fenway]

Did you run ANALZYE TABLE recently?

[neoform]

yep. It auto runs every 6 hours via cronjob. (and i just ran it right now to be sure)..

 

same result.

 

Could it be that it's being cause by the version I'm using? 5.1.26-rc-community

[fenway]

Well, it's "flipped" around your tables... did you try STRAIGHT JOIN?

[neoform]

Hah, hadn't thought to do that one... I was starting to think I was going crazy. Seems it was just mysql thinking it was smarter than me and using no index instead of the one i provided.

 

Works. Thanks