CODEWITHSUNDEEP
c#mysqldatabasedatabase-performance
skt
skt

Reputation: 599

Mysql query takes more than 5 minutes

I have a MySQL database that has multiple tables. The SQL query in question will take more than 5 minutes to execute. One table that is queried is 'DeviceInfo' and has more than 21 Millions records.

The (C#) application executing this query receives a timeout during the execution of the query.

I have increased the innodb_buffer_pool_size = 64M to 1G in My.ini for MySQl.

How can I reduce the execution time. 

SELECT DISTINCT(pd.PatientId)   FROM DeviceInfo di   
  JOIN DeviceSession ds ON ds.DeviceSessionID = di.ByDevSessionId   
  JOIN UserSession us ON us.UserSessionId = ds.ByUserSessionId   
  JOIN PatientDetails pd ON pd.PatientDetailsId = us.ByPatientId   
  WHERE   di.SyncStatus = 0   ORDER BY us.UserStartSessionTime  Desc

The DeviceInfo table has 21,850,806 records

Upvotes: 2

Views: 763

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93754

Since you want just check the distinct PatientId from PatientDetails table try converting all the Joins to exists which will have better performance than Join

Also You can remove the order by at the end if you just want to see the distinct PatientId with out any order 

SELECT pd.PatientId
FROM   PatientDetails pd
WHERE  EXISTS (SELECT 1
               FROM   UserSession us
               WHERE  pd.PatientDetailsId = us.ByPatientId
                      AND EXISTS (SELECT 1
                                  FROM   DeviceSession ds
                                  WHERE  us.UserSessionId = ds.ByUserSessionId
                                         AND EXISTS (SELECT 1
                                                     FROM   DeviceInfo di
                                                     WHERE  ds.DeviceSessionID = di.ByDevSessionId
                                                            AND di.SyncStatus = 0)))
GROUP  BY pd.PatientId

Upvotes: 1

Hatem Jaber
Hatem Jaber

Reputation: 2402

do you have indexes on those columns that you're joining and on the one in your where clause? Use the word `EXPLAIN SELECT...' before your query and look at what is output in the mysql cli, that will let you know if it is doing a full table scan or...

Upvotes: 0

Related Questions