Re: Get rid of Temporary/filesort, rather simple query

[Reine n/a]

Hi Rick,

I have a PM system that works like conversations in a forum, except it's not threaded.
In pm2user I have the topic stored for each participant and in pm the actual messages.

Here is a little updated version of the query

SELECT
     IF(p2u.last_viewed<pm.datetime,true,false) AS unread,
     pm.conversation_uuid,
     pm.subject,
     pm.datetime
FROM
     pm2user AS p2u,
     pm
WHERE
     p2u.conversation_uuid=pm.conversation_uuid
     AND  p2u.user_id=1
     AND  p2u.folder_id=1
GROUP BY
     p2u.conversation_uuid
ORDER BY
     pm.datetime DESC
LIMIT
     0, 15;

And the EXPLAIN..

id	select_type	table	type	key	rows	Extra
1	SIMPLE	p2u	index	index2	31728	Using where; Using index; Using temporary; Using filesort
1	SIMPLE	pm	ref	uuid_datetime	3

Let me explain the query from my view.
IF(p2u.last_viewed<pm.datetime,true,false) AS unread
Just to see if anyone have posted any new messages after the conversation was viewed last.

pm.conversation_uuid
This is the unique identifier of the whole conversation

pm.subject
self explaining...

pm.datetime
When the message was posted

GROUP BY pm.conversation_uuid
To only get one instance of each conversation

ORDER BY pm.datetime
This can also be ORDER BY pm.id, I just want them ordered in the way they were posted.

CREATE TABLE `pm` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `conversation_uuid` char(36) NOT NULL,
  `sender_id` int(10) unsigned NOT NULL,
  `subject` varchar(150) NOT NULL,
  `message` text NOT NULL,
  `datetime` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `uuid_datetime` (`conversation_uuid`,`datetime`),
  KEY `sender_id_datetime` USING BTREE (`sender_id`,`datetime`)
) ENGINE=InnoDB AUTO_INCREMENT=470502 DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 1642496 kB'

Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment
pm	InnoDB	10	Compact	135635	1760	238731264	0	13713408	0	470502	2009-05-24 14:56:19			latin1_swedish_ci			InnoDB free: 1642496 kB; InnoDB free: 1908736 kB

CREATE TABLE `pm2user` (
  `conversation_uuid` char(36) NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `folder_id` int(10) unsigned NOT NULL,
  `invited` datetime NOT NULL,
  `last_viewed` datetime NOT NULL,
  KEY `index2` USING BTREE (`conversation_uuid`,`user_id`,`folder_id`,`last_viewed`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 1905664 kB'

Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment
pm2user	InnoDB	10	Compact	42303	111	4734976	0	4767744	0		2009-05-25 21:04:23			latin1_swedish_ci			InnoDB free: 1905664 kB; InnoDB free: 1908736 kB

Edited 3 time(s). Last edit at 06/03/2009 01:14AM by Reine n/a.