Re: Nested Sets vs. Nested Intervals

From: asdf <xyz91234_at_yahoo.com>
Date: 19 Nov 2005 10:17:49 -0800
Message-ID: <1132424269.325234.297120_at_g49g2000cwa.googlegroups.com>


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`),
  KEY `name` (`name`,`a11`,`a12`,`a21`,`a22`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE `listings` (
`id` int(8) NOT NULL auto_increment,
`cat_id` int(6) NOT NULL,
`url` varchar(150) NOT NULL,
`title` varchar(100) NOT NULL,
`description` text NOT NULL,

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

Suppose I want to search the listings. The search results are ordered by relevency. If it matches the listing.title or the listing.description, it is a result.

Now suppose I want to restrict the search results to a subcategory. How can I do it efficently? That is, without reading much disk space, without wasting much memory, and without checking if each result if it's in the subcategory.

The Yahoo! Directory, Google Directory, and the Open Directory Project uses its search engine instead of searching the directory database. That's why their directory searches are fast.

It seems there isn't any use of nested intervals. So I should continue using the adjacency model and a search engine instead of the nested intervals model. In that way the searches are more accurate because the search engine can index the web pages' text instead of only matching the title and the description. Is there any useful reason for using the nested intervals model for a web directory? Received on Sat Nov 19 2005 - 19:17:49 CET

Original text of this message