Re: Nested Sets vs. Nested Intervals

From: asdf <xyz91234_at_yahoo.com>
Date: 2 Dec 2005 20:49:35 -0800
Message-ID: <1133585375.759732.14990_at_f14g2000cwb.googlegroups.com>


asdf wrote:

> I hate the nested intervals model because the adjacency list model can
> just be as efficient as that model. Nested intervals is useless for a
> web directory.

Thank you very much, but it's bad to store the category name on every listing on each category. You said that you can count the listings very efficiently using the adjacency list model?

I don't want this:

CREATE TABLE `directorylistings` (
`lid` int(8) unsigned NOT NULL,
`cid` int(8) unsigned NOT NULL,
`name` varchar(100) NOT NULL,
`a11` int(8) unsigned NOT NULL,
`a12` int(8) unsigned NOT NULL,
`a21` int(8) unsigned NOT NULL,
`a22` int(8) unsigned NOT NULL,
`url` varchar(150) NOT NULL,
`title` varchar(100) NOT NULL,
`description` text NOT NULL,

  PRIMARY KEY (`lid`),

  KEY `right_col` (`a12`,`a22`),
  KEY `atom` (`a11`,`a12`,`a21`,`a22`),
  KEY `left_col_name` (`name`,`a12`,`a22`),
  KEY `cid_name` (`cid`,`name`),
  KEY `url` (`url`),

  FULLTEXT KEY `description` (`description`),
  FULLTEXT KEY `title` (`title`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

because it's a de-normalized representation of the data and wastes space.

I would do something like this:

CREATE TABLE `directory` (
`id` int(6) unsigned NOT NULL auto_increment,
`name` varchar(100) NOT NULL,
`a11` int(8) unsigned NOT NULL,
`a12` int(8) unsigned NOT NULL,
`a21` int(8) unsigned NOT NULL,
`a22` int(8) unsigned NOT NULL,

  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`,`a11`,`a12`),   KEY `parent` (`a21`,`a22`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE `listings` (
`listing_id` int(8) unsigned NOT NULL auto_increment,
`cat_id` int(6) unsigned NOT NULL default '0',
`url` varchar(150) NOT NULL,
`title` varchar(100) NOT NULL,
`description` text NOT NULL,
`rating` tinyint(2) unsigned NOT NULL default '0',
`lft_index` double unsigned NOT NULL default '0',
`rgt_index` double unsigned NOT NULL default '0',

  PRIMARY KEY  (`listing_id`),
  KEY `cat_id` (`cat_id`),
  KEY `lft_index` (`lft_index`),
  KEY `rgt_index` (`rgt_index`),
  KEY `rating` (`rating`),

  FULLTEXT KEY `description` (`description`),   FULLTEXT KEY `title` (`title`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

so you can select the 10 top rated listings in a specific category efficiently by doing this:

SELECT listings.*
FROM listings
WHERE directory.a11*node.a21 >= directory.a21*node.a11 AND directory.a11*node.a22 >= directory.a21*node.a12 AND listings.lft_index

	BETWEEN node.a11/node.a21 - 0.00000001 AND
	(node.a11-node.a12)/(node.a21-node.a22) + 0.0000001
ORDER BY listings.rating DESC LIMIT 0, 10

so you can select the websites within a specific category that contains "Software" by doing this:

SELECT listings.*
FROM listings

WHERE directory.a11*node.a21 >= directory.a21*node.a11 AND directory.a11*node.a22 >= directory.a21*node.a12 AND listings.lft_index

	BETWEEN node.a11/node.a21 - 0.00000001 AND
	(node.a11-node.a12)/(node.a21-node.a22) + 0.0000001
AND listings.title LIKE '%Software%'

Is there a better way to do this?

You said the adjacency relation can search the listings within a specific category efficiently. How can I do that with the adjacency relation?

Thank you very much. Received on Sat Dec 03 2005 - 05:49:35 CET

Original text of this message