mysql optimize or simplify SQL statements-CodePudding

mysql table is

intersection record basic information

relation record intersection and roadside relation, intersection and intersection relation

code

SELECT c.*,d.obj_name as end_name,e.obj_name as start_name from (
    SELECT a.source_key as start_key,b.source_key as end_key,a.target_key as roadside_key FROM 
    (SELECT * FROM `brelation` WHERE 
        source_key='c1_VbFljpol' and target_concept='roadside' and rel_number=1) as a
    JOIN
    (SELECT * FROM `brelation` WHERE source_key in 
    (SELECT target_key FROM `brelation` WHERE 
        source_key='c1_VbFljpol' and target_concept='node') 
        and target_concept='roadside边' and rel_number=2) as b
    ON
    a.target_key=b.target_key
    ) as c
JOIN bintersection as d
ON
c.end_key=d.obj_key
JOIN bintersection as e
ON
c.start_key=e.obj_key

final reslut

start_key   start_name end_key   roadside_key end_name
c1_VbFljpol aaaa    c1_hKwEo6JZ c3_G2rSzUIK bbbb
c1_VbFljpol aaaa    c1_gDUWuB4V c3_YtKWzPy0 cccc
c1_VbFljpol aaaa    c1_uayODvZz c3_SMz1WGl0 dddd

CodePudding user response：

Wow... what a mess of a query.. However, when rewritten, is this what you are ultimately looking for?

SELECT 
        br.source_key Start_Key,
        br2.source_key End_Key,
        br.target_key Roadside_Key,
        bi2.obj_name as end_name,
        bi.obj_name as start_name 
    FROM 
        brelation br
            JOIN bintersection as bi
                ON br.source_key = bi.obj_key

            JOIN brelation br2
                on br.target_key = br2.target_key
                AND br.source_key = br2.source_key
                AND br2.target_concept = 'node'
                AND br2.rel_number = 2

                JOIN bintersection as bi2
                    ON br2.source_key = bi2.obj_key
    WHERE
            br.source_key = 'c1_VbFljpol' 
        and br.target_concept = 'roadside' 
        and br.rel_number = 1

Your inner sub-select queries creating alias A and B are targeting the same source, but "rel_number" = 1 and Roadside vs "rel_number" = 2 and Node. So just join those as they are. Now, your BIntersection tables. I am using those with respective aliases as well from BR/BI to BR2/BI2 respectively for the start and end context.

You dont need to specificy the source key twice since whatever is in the main table is the basis joining to the second instance BR2 by same key value.

I THINK this is what you are looking for.

FEEDBACK

Nothing was mentioned about performance... For that and knowing that the query provided is cleaner and is doing what you need, now we can look at indexes. For this query, I would suggest a proper multi-column index on the tables as follows.

table         Index
brelation     (Source_Key, Target_Concept, Rel_Number)
bintersection (Obj_Key, obj_name)