query problem
Posted by:
Tommy Tommy
Date: September 27, 2018 04:11AM
Hello,
I'm having problem understaing how query works. I have query that return hierarchy of rows and it works differenty when using index-es that without it.
Could you please help me understand why.
Example of table
CREATE TABLE `hierarchical` (
`id` int(11) NOT NULL,
`parent_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `hierarchical` (`id`, `parent_id`) VALUES
(1, NULL),
(4, 1),
(3, 2),
(2, 4),
(5, 4),
(6, 5);
ALTER TABLE `hierarchical`
ADD PRIMARY KEY (`id`),
ADD KEY `parent_idx` (`parent_id`);
ALTER TABLE `hierarchical`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
SQL that works:
SELECT id,
parent_id
FROM (SELECT * FROM hierarchical IGNORE INDEX (parent_idx)
ORDER BY parent_id, id) AS directories_sorted,
(SELECT @pv := 4) AS initialisation
WHERE find_in_set(parent_id, @pv)
AND LENGTH(@pv := concat(@pv, ',', id))
Not working (not returning hierarchy of id 2):
SELECT id,
parent_id
FROM (SELECT * FROM hierarchical USE INDEX (parent_idx)
ORDER BY parent_id, id) AS directories_sorted,
(SELECT @pv := 4) AS initialisation
WHERE find_in_set(parent_id, @pv)
AND LENGTH(@pv := concat(@pv, ',', id))