Is there a better optimized/efficient way of doing this

Databases
1

There has to be a better way then doing this, obviously SQL is not one of my strong points, which I’m trying to correct now lol
Should I be using sub-queries?


SELECT
        sr.id
        , sr.description
        , sr.report
        , sr.note AS internalNote
        , st.name AS serviceType
        , wo.id AS woid
        , c.name AS companyName
        , c.id AS cid
        , DATE_FORMAT(sr.dateCreated,'%m/%d/%Y @ %h:%i %p') AS dateCreated
        , ROUND((TIME_TO_SEC(sr.endtime) -
                         TIME_TO_SEC(sr.starttime)) / 3600.0 , 2 ) AS totalHours
        , ROUND((TIME_TO_SEC(sr.endtime) -
                         TIME_TO_SEC(sr.starttime)) / 3600.0 , 2 ) - sr.deduction AS totalTime
        , DATE_FORMAT(sr.startTime,'%c/%e/%Y - %h:%i %p') AS startTime    
        , DATE_FORMAT(sr.endTime,'%c/%e/%Y - %h:%i %p') AS endTime
        , sr.deduction
        , s.name AS status
        , e.mfrName
        , e.model
        , e.serial
        , l.name AS locationName
        , srn.note
        , DATE_FORMAT(srn.postedOn,'%m/%d/%Y @ %h:%i %p') AS noteDate
        , t.firstName
        , t.lastName
        , rt.name as rateName
FROM
        service_report AS sr
LEFT JOIN
        work_orders as wo ON
        wo.id = sr.woid
LEFT JOIN
        company AS c ON
        sr.cid = c.id
LEFT JOIN
        enum_status as s ON
         sr.status = s.id
LEFT JOIN
        equipment as e ON
         sr.eid = e.id
LEFT JOIN
        locations as l ON
        e.location = l.id
LEFT JOIN
        enum_service_type as st ON
        sr.serviceType = st.id
LEFT JOIN
        service_report_notes as srn ON
        sr.id = srn.srid
LEFT JOIN
        technicians as t ON
        srn.postedBy = t.id
LEFT JOIN
        enum_rate_type as rt ON
        sr.rateType = rt.id                                                     
WHERE
        sr.id = ?
2

Do you have indexes on the columns you are joining on?

3

Only Primaries on the id’s, some have Unique on certain fields

4

here’s the FROM clause from your query –


FROM
        service_report AS sr
LEFT JOIN
        work_orders as wo ON
        [COLOR="#0000FF"]wo.id[/COLOR] = [COLOR="#FF0000"]sr.woid[/COLOR]
LEFT JOIN
        company AS c ON
        [COLOR="#0000FF"]c.id[/COLOR] = [COLOR="#FF0000"]sr.cid[/COLOR]
LEFT JOIN
        enum_status as s ON
        [COLOR="#0000FF"]s.id[/COLOR] = [COLOR="#FF0000"]sr.status[/COLOR]
LEFT JOIN
        equipment as e ON
        [COLOR="#0000FF"]e.id[/COLOR] = [COLOR="#FF0000"]sr.eid[/COLOR] 
LEFT JOIN
        locations as l ON
        [COLOR="#0000FF"]l.id[/COLOR] = [COLOR="#FF0000"]e.location[/COLOR]
LEFT JOIN
        enum_service_type as st ON
        [COLOR="#0000FF"]st.id[/COLOR] = [COLOR="#FF0000"]sr.serviceType[/COLOR]
LEFT JOIN
        service_report_notes as srn ON
        [COLOR="#FF0000"]srn.srid[/COLOR] = [COLOR="#0000FF"]sr.id[/COLOR]
LEFT JOIN
        technicians as t ON
        [COLOR="#0000FF"]t.id[/COLOR] = [COLOR="#FF0000"]srn.postedBy[/COLOR]
LEFT JOIN
        enum_rate_type as rt ON
        [COLOR="#0000FF"]rt.id [/COLOR]= [COLOR="#FF0000"]sr.rateType[/COLOR]

i’m guessing the columns in blue are primary keys, therefore they do not need an (additional) index

the columns in red would be ones that you should consider indexing

aside: notice anything about the pattern of reds and blues? (yes, i did have to switch some of them around for you)

each service report has exactly one matching row from all the other tables, but possibly multiple rows from the service report notes table

5

Ok, so I will add indexes for the red’s, thank you

so yeah, each service report can have multiple notes assigned to it, am I doing something wrong?

6

not at all

it was just an observation, an example of understanding the one-to-many relationships in a somewhat complex query by analyzing the column names for obvious primary keys