MySQL Forums
Forum List  »  Newbie

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))

Options: ReplyQuote


Subject
Written By
Posted
query problem
September 27, 2018 04:11AM
September 27, 2018 03:03PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.