0

I have a Modified Preorder Tree Traversal (MPTT) table with 82117 records. This mysql table contains some geographical data (Country, State, Region, City...)

CREATE TABLE IF NOT EXISTS `geotree` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `geo_id` int(11) DEFAULT NULL,
  `name` varchar(80) COLLATE utf8_unicode_ci NOT NULL,
  `lft` int(11) NOT NULL,
  `rgt` int(11) NOT NULL,
  `parent` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`,`lft`,`rgt`),
  KEY `geo_id` (`geo_id`),
  KEY `parent` (`parent`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=82174 ;

The problem comes when I try to get a path using a standard MPTT query like this:

SELECT p.name 
FROM `geotree` AS node, `geotree` AS p 
WHERE node.lft BETWEEN p.lft AND p.rgt AND node.rgt='$value'
ORDER BY p.lft

It takes more than 2.5 seconds to complete. What's the problem? I have missed any index? Too much rows for this type of data structure? Or there is any mistake in the query?

NOTE: I've noted that if I remove the "ORDER BY" from the query, the query time decreases to 0.05 seconds.

Ivan
  • 14,692
  • 17
  • 59
  • 96
  • Unfortunately in MySQL join's can't use indexes on range conditions (`BETWEEN`,`<`,`>`,...). In this case the run time becomes proportional to * . – Vatev Nov 30 '15 at 19:42

1 Answers1

0

You should at least transform your query to proper JOIN that must bring some performance:

http://sqlfiddle.com/#!9/b4d7f8/3

SELECT p.name 
FROM `geotree` AS node
INNER JOIN `geotree` AS p 
ON node.lft BETWEEN p.lft AND p.rgt 
WHERE node.rgt='$value'

And since you need node.rgt='$value' as a filter I would add index KEY rgt (rgt)

Alex
  • 16,739
  • 1
  • 28
  • 51
  • To this query I have to add the "ORDER BY p.lft" to get the correct order. Once I do it, it takes 2.4 seconds, a bit faster, but not enought. The same after adding "KEY rgt (rgt)" takes 0.25 seconds, which is very close to the expected performance, but still not enough (+1 for this!). – Ivan Nov 30 '15 at 20:35
  • This is exactly the same as OP's original query. Why would you think it will have different performance? – Vatev Nov 30 '15 at 21:04