FIND_IN_SET() in mysql query makes the query very slow?

Question

Here is my query where I am getting list of all children categories for parent category which is 3

SELECT GROUP_CONCAT(lv SEPARATOR ',') 
FROM ( 
       SELECT @pv:=( 
                     SELECT GROUP_CONCAT(id SEPARATOR ',') 
                     FROM ad_category 
                     WHERE is_active = 1 
                       AND FIND_IN_SET(parent_id, @pv)
                   ) AS lv 
       FROM ad_category
       JOIN (SELECT @pv:=3)tmp
       WHERE FIND_IN_SET(parent_id, @pv)
     ) a;

The above query is taking 7 sec time to getting 2100 id with , in string format. I need the query to get results in millisecond. How I can optimized this query optimized query. Any help is nicely appreciable?

Here my category table format

///// MYSQL CATEGORY table
id             name               parent_id
 1             shoes                  0
 2             blue shoes             1
 3             men blue shoes         2
 4             men blue shoe small    3
 5             red shoes              1
 6             men red shoes          5

Pleaes provide sample data and desired results to explain what you are doing. — Gordon Linoff, Feb 26 '21 at 11:56
Please provide the task itself, not your attempt to solve it. — Akina, Feb 26 '21 at 12:32
Does this answer your question? [How to create a MySQL hierarchical recursive query?](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) — P.Salmon, Feb 26 '21 at 12:32
You're using a Beta version of MySQL from 2013, when there have 30 releases of the 5.7 branch since then? — Bill Karwin, Feb 26 '21 at 13:50
You might like my post about querying hierarchical data: https://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree/192462#192462 — Bill Karwin, Feb 26 '21 at 13:51
I would also recommend not getting into the habit of depending on `:=` variable assignment as a side-effect in expressions, because that practice has become deprecated in MySQL 8.0. — Bill Karwin, Feb 26 '21 at 13:53

FIND_IN_SET() in mysql query makes the query very slow?

0 Answers0