Inner subquery optimization

Saeven

Hi Everyone,

First accept my appreciation for any time you can take to help. I've banged my head against this one for a bit, and am unsure as to how I can achieve what I require in a non-programmatical way - it would be nice to have one query do all of the work. I've also consulted the mysql forums with this question without luck (although there were many responses):

Consider the following three tables from the schema:

authorization

CREATE TABLE `authorization` (
`username` varchar(40) NOT NULL default '',
`password` varchar(12) NOT NULL default '',
`validated` int(1) NOT NULL default '0',
`customer_id` bigint(20) unsigned NOT NULL default '0',
`rights` int(32) NOT NULL default '0',
`vkey` tinytext NOT NULL,
PRIMARY KEY (`username`),
KEY `customer_id` (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

topics

CREATE TABLE `topics` (
`id` int(32) unsigned NOT NULL auto_increment,
`category` varchar(12) NOT NULL default '0',
`subject` varchar(64) NOT NULL default '',
`priority` varchar(128) NOT NULL default '',
`initiator` bigint(20) unsigned NOT NULL default '0',
`date` datetime NOT NULL default '0000-00-00 00:00:00',
`status` int(1) unsigned NOT NULL default '1',
`emailed` int(1) NOT NULL default '0',
`extra` text NOT NULL,
`flags` int(32) unsigned NOT NULL default '0',
`public` int(1) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `initiator` (`initiator`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Topic Index'

messages

CREATE TABLE `messages` (
`id` int(32) unsigned NOT NULL auto_increment,
`topic_id` int(32) unsigned NOT NULL default '0',
`user` bigint(20) unsigned NOT NULL default '0',
`date` datetime NOT NULL default '0000-00-00 00:00:00',
`message` text NOT NULL,
PRIMARY KEY (`id`),
KEY `topic_id` (`topic_id`),
KEY `date` (`date`),
KEY `user` (`user`),
FULLTEXT KEY `search` (`message`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Message Container'

On these tables I am running the following query, which does the trick:

SELECT topics.*, X.mdate, authorization.username
FROM (topics LEFT JOIN
( SELECT MAX( date ) mdate, topic_id, user
FROM messages
GROUP BY topic_id
) AS X ON topics.id = X.topic_id )
LEFT JOIN authorization ON authorization.customer_id = X.user
WHERE topics.initiator='3433' AND topics.status != '4'
GROUP BY id ORDER BY topics.category, X.mdate;

EXPLAINing this query however, shows the following understandable results, which initially not a problem, are really starting to affect performance since the size of the message table is quickly growing:

"id","select_type","table","type","possible_keys", "key","key_len","ref","rows","Extra"
1,"PRIMARY","topics","ref","initiator","initiator" ,8,"const",26,"Using where; Using temporary; Using filesort"
1,"PRIMARY","<derived2>","ALL","","",(null),"",416 6,""
1,"PRIMARY","authorization","ref","customer_id","c ustomer_id",8,"X.user",1,""
2,"DERIVED","messages","ALL","","",(null),"",23685 ,"Using temporary; Using filesort"

The inner SELECT MAX( date ) query is scanning all of the 23685 rows in the message table to find the actual max. When in fact, it should in the best case, only scan the rows that belong to the topic that matches the topic being scanned in the outer query.

SELECT COUNT(*) AS total
FROM messages LEFT JOIN topics ON messages.topic_id = topics.id
WHERE topics.initiator='3433' AND topics.status !='4'

Yields that there are 341 message rows to match the topics with initiator 3433 and status != 4.

The question then, is how to formulate the query such that it only scans appropriate rows for the Max subquery?

Help most appreciated!

Cordially.
Alex

tomhath

I don't see why you need the subselect at all.

Just join to the messages table and group by all the columns you are returning from the other tables and take the MAX(date) from messages.

Am I missing something here?

Saeven

MYSQL doesn't guarantee any ordering when grouping however. What you suggest is how this query was originally performed, but the appropriate MAX values weren't always chosen.

tomhath

I don't understand what you mean by "MYSQL doesn't guarantee any ordering when grouping". The result set is ordered according to the ORDER BY clause, isn't it?

SELECT topics.id, 
	max(messages.date) mdate, 
	topics.category, 
	topics.subject, 
	topics.priority, 
	topics.initiator, 
	topics.date, 
	topics.status, 
	topics.emailed, 
	topics.extra, 
	topics.flags, 
	topics.public, 
	authorization.username
FROM topics 
LEFT JOIN messages
ON messages.topic_id = topics.topic_id 
LEFT JOIN authorization 
ON authorization.customer_id = messages.user
WHERE topics.initiator='3433' AND topics.status != '4'
GROUP BY topics.id, 
	topics.category, 
	topics.subject, 
	topics.priority, 
	topics.initiator, 
	topics.date, 
	topics.status, 
	topics.emailed, 
	topics.extra, 
	topics.flags, 
	topics.public, 
	authorization.username
ORDER BY 3,2;

Saeven

The result set is ordered according to the ORDER BY clause, isn't it?

When using GROUP BY, it is not. The ORDER is applied after the grouping, the results could be random.

Also, the query you have posted, would return many hits per single topic id. The goal is to get the topic left joined to the message that has the latest date.

The topics and messages are on a 1:n relationship, so where many messages can belong to one topic, if you simply left join topics to messages, you will get a row containing every topic and every message. When you group by topic id, you are performing the essential shaving off of duplicate topic ids, however, this might not as mentioned above, retain the wanted message rows with highest ID - hence why it is being done as is currently shown in the first topic, ensuring that only the MAX message is joined.

This isn't an obvious problem, I'm not sure it even has a solution...

Cheers.
Alex

tomhath

Did you try what I posted?

It groups by topic (so you'll only get one row per topic), it takes the MAX(date) from messages so you'll get the one you want for each topic, then it orders the result set by topics category.

I tested this, it looks to me that it gives the desired result:

SELECT topics.id, max( messages.date ) mdate, topics.category, topics.subject, topics.priority, topics.initiator, topics.date, topics.status, topics.emailed, topics.extra, topics.flags, topics.public, authorization.username
FROM topics
LEFT  JOIN messages ON messages.topic_id = topics.id
LEFT  JOIN authorization ON authorization.customer_id = messages.user
WHERE topics.initiator =  '3433' AND topics.status !=  '4'
GROUP  BY topics.id, topics.category, topics.subject, topics.priority, topics.initiator, topics.date, topics.status, topics.emailed, topics.extra, topics.flags, topics.public, authorization.username
ORDER  BY 3 , 2

Saeven

Yes I did try your query just now, it returned two rows per topic in fact, and the dates joined were not the latest in all cases. As writ, the order of application is not guaranteed. I suppose with a small data set you might be lucky, but the problem surfaces when the selection entropy is high on the loss incurred by the GROUP action.

tomhath

If you say so. But I'm assuming you're a troll.