SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Hybrid View

  1. #1
    SitePoint Zealot
    Join Date
    Oct 2004
    Location
    naperville
    Posts
    189
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Modified Preordered Tree Traversal Query

    Hi, I'm trying to rework my tree traversal query as too do more of the DB work in MySQL and clean up the PHP code. I'm trying to avoid using subselects as I want to stay compatabile with MySQL3x.

    This is what I have right now, using a temporary table:
    Code:
    CREATE TEMPORARY TABLE tmp_par_ids (
    	left_id INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
    	right_id INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL);
    INSERT INTO tmp_par_ids SELECT `left_id`, `right_id` from data where `id` = 4;
    SELECT SQL_CACHE
    	`id` ,
    	data.right_id,
    	data.left_id,
    	`title` ,
    	`level`,
    	REPEAT(' ', `level`-1) html_level_spaces
    FROM
    	data,tmp_par_ids
    WHERE
    	`level` <= 5 AND
    	data.left_id >= tmp_par_ids.left_id AND
    	data.right_id <= tmp_par_ids.right_id
    ORDER BY
    	`left_id`;
    DROP TABLE tmp_par_ids;
    I would like to know if its possible to accomplish this in one query by selecting the table twice, using aliases, and do a inner/left join on itself to get the parent right / left ids - I'm not entirely sure how possible this is.

    Also, what are the performance benefits with each solution?

    This is my table set up:
    Code:
    CREATE TABLE `data` (
      `id` mediumint(8) unsigned NOT NULL auto_increment,
      `title` varchar(200) NOT NULL default '',
      `left_id` int(10) unsigned NOT NULL default '0',
      `right_id` int(10) unsigned NOT NULL default '0',
      `level` mediumint(8) unsigned NOT NULL default '0',
      UNIQUE KEY `id` (`id`)
    ) TYPE=MyISAM AUTO_INCREMENT=12 ;
    
    #
    # Dumping data for table `data`
    #
    
    INSERT INTO `data` VALUES (1, 'Music', 1, 14, 1);
    INSERT INTO `data` VALUES (2, 'Ludo', 2, 5, 2);
    INSERT INTO `data` VALUES (3, 'self titled', 3, 4, 3);
    INSERT INTO `data` VALUES (4, 'weezer', 6, 13, 2);
    INSERT INTO `data` VALUES (5, 'pinkerton', 7, 12, 3);
    INSERT INTO `data` VALUES (6, 'el scorcho lyrics', 8, 9, 4);
    INSERT INTO `data` VALUES (7, 'pink triangle lyrics', 10, 11, 4);
    INSERT INTO `data` VALUES (8, 'Foods', 15, 22, 1);
    INSERT INTO `data` VALUES (9, 'Meat', 16, 21, 2);
    INSERT INTO `data` VALUES (10, 'Steak', 17, 18, 3);
    INSERT INTO `data` VALUES (11, 'ribs', 19, 20, 3);
    Thanks,
    Phil

  2. #2
    SitePoint Zealot
    Join Date
    Oct 2004
    Location
    naperville
    Posts
    189
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Apparently, it is I need to benchmark real quick, but I'm very sure this one will be faster.

    Code:
    SELECT SQL_CACHE
    	nodes.id,
    	nodes.right_id, 
    	nodes.left_id, 
    	nodes.level, 
    	nodes.title,
    	REPEAT('&nbsp;', nodes.level-1) html_level_spaces
    FROM
    	data as nodes, data as parent
    WHERE
    	nodes.left_id >= parent.left_id AND
    	nodes.right_id <= parent.right_id AND
    	parent.id = 4 AND
    	nodes.level < 5
    ORDER BY
    	nodes.left_id


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •