Hi,
here my table which stores multiple trees with nested sets:
CREATE TABLE `posts` (
`posting_id` int(11) unsigned NOT NULL auto_increment,
`root_id` int(11) unsigned NOT NULL,
`lft` int(11) unsigned NOT NULL,
`rgt` int(11) unsigned NOT NULL,
`subject` varchar(400) collate latin1_german1_ci NOT NULL,
`posting_date` datetime NOT NULL,
-- some additional fields
PRIMARY KEY (`posting_id`),
UNIQUE KEY `id_und_lft` (`posting_id`,`lft`),
KEY `root_id` (`root_id`),
KEY `username` (`username`),
KEY `root-id_und_lft` (`root_id`,`lft`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;
I can select all trees with the following SELECT:
SELECT a.posting_id, a.root_id, a.username, a.subject, count(*) AS level
FROM posts AS a
JOIN posts AS b ON (a.lft BETWEEN b.lft AND b.rgt)
AND (b.root_id = a.root_id)
GROUP BY a.posting_id
ORDER BY a.root_id,a.lft
Works fine, uses indexes nicely.
Now I want to display those trees in reverse chronological order.
(The date of a tree is determined by the `posting_date` field of its root)
That's not a big problem since the joining is already done correctly.
SELECT a.posting_id, a.root_id, a.username, a.subject, count(*) AS level
FROM posts AS a
JOIN posts AS b ON (a.lft BETWEEN b.lft AND b.rgt)
AND (b.root_id = a.root_id)
GROUP BY a.posting_id
ORDER BY b.posting_date DESC, a.root_id,a.lft
But here's the problem. Since the results are now ordered by fields from
more than one table, indexes no longer work and filesort is used.
So how bad is this? The mentioned query will be the query which is used
the most in my application (yes, it is going to be a forum).
Should I break normalization and save the date of the root in each node row?
Greets, John
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
