List:General Discussion« Previous MessageNext Message »
From:John Smith Date:August 26 2008 6:11pm
Subject: Normalization vs. Performance
View as plain text  
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

Thread
Normalization vs. PerformanceJohn Smith26 Aug
  • Re: Normalization vs. PerformanceKevin Hunter29 Aug
    • RE: Normalization vs. PerformanceJerry Schwartz29 Aug
      • Re: Normalization vs. PerformanceArthur Fuller29 Aug
  • Re: Normalization vs. PerformanceMartijn Tonies29 Aug