This is my SQL
SELECT
ID. LEVEL1,
DATA1. *
The FROM
(
SELECT
@ ids AS _ids,
(SELECT @ ids:=GROUP_CONCAT (gid) FROM project_grouping WHERE FIND_IN_SET (parent_id, @ ids)) AS cids,
@ : l=@ l + 1 AS LEVEL1
The FROM
Project_grouping,
(SELECT @ ids:=# {parent_id}, @ : l=1) b
WHERE
@ ids IS NOT NULL
) ID,
Project_grouping DATA1
WHERE
FIND_IN_SET (DATA1. Gid, ID. _ids)
AND gid=# {gid}

The ORDER BY
LEVEL1,
Gid


query structure
this problem bothering me too much, with two days, can add cent, who deliver me

CodePudding user response:

The first query or article 15 second is only five

CodePudding user response:

It takes some time to understand demand ah, good complex

CodePudding user response:

The performance must be very poor, but also may be a deadlock, overlock wait

CodePudding user response:

reference 3/f change the pain response:

it must have poor performance, also may be a deadlock, overlock wait

This refers to a recursive query, why would a deadlock from online copy bosses

CodePudding user response:

Is easy to appear, does not represent a certain, especially when concurrent, because you carry out the long time of resources, will lock table, under the best resolution, relatively independent,

CodePudding user response:

refer to fifth floor metamorphosis of pain response:

is easy to appear, does not represent a certain, especially when concurrent, because you carry out the long time of resources, will lock table, it is best to split, relatively independent,

What do you think how long takes up resources, how long this SQL actually did not perform, 0.018 s, it will lock table, it is a lock table cause is a problem with the query results
The first 15 second 5

CodePudding user response:

Try how long data volume to 200000

CodePudding user response:

The layer of the incremental based data

CodePudding user response:

refer to 7th floor metamorphosis of pain response:

how long the data volume to 200000 try

Haha I haven't contact to such a large amount of data, the bosses the SQL can also optimize the