CODEWITHSUNDEEP
mysqlsql
Hia
Hia

Reputation: 103

how to simplify my sql query

I have this query, but it takes about 15 seconds to finish.. how can i simplyfy it to get same result in less time? my problem is that i need all of this data at ones.

SELECT * FROM (
    SELECT c.client_id, c.client_name, c.client_bpm,
                   c.client_su_name, c.client_maxbpm, s.bpm, 
                   s.timestamp, m.mesure_id, ms.currentT
    FROM tbl_clients c, tbl_meting m, tbl_sensor_meting s, 
                        tbl_magsens_meting ms
    WHERE c.client_id = m.client_id
    AND (m.mesure_id = s.id_mesure
            OR m.mesure_id = ms.id_mesure)
    AND m.live =1
    ORDER BY s.timestamp DESC
        ) AS mesure
          GROUP BY mesure.client_id

Upvotes: 1

Views: 93

Answers (1)

Skytunnel
Skytunnel

Reputation: 1083

I think the problem may be the OR condition from your WHERE clause? You seem to be trying to join to one table or another, which you can't do. So I've replaced it with a LEFT JOIN, so in the event no related records exist nothing will be returned.

I also took out your GROUP BY, as I don't think it was required.

SELECT c.client_id, c.client_name, c.client_bpm,
               c.client_su_name, c.client_maxbpm, s.bpm, 
               s.timestamp, m.mesure_id, ms.currentT
FROM         tbl_clients         c
  JOIN       tbl_meting          m   ON m.client_id = c.client_id
  LEFT JOIN  tbl_sensor_meting   s   ON s.id_mesure = m.mesure_id
  LEFT JOIN  tbl_magsens_meting  ms  ON ms.id_mesure = m.mesure_id
WHERE m.live = 1
ORDER BY s.timestamp DESC

Upvotes: 2

Related Questions