Display last value for each user in SQL (fast)

132
September 07, 2018, at 1:50 PM

I'm trying to find if the last action for a user in the last 24 hours was in and not out (it's a software for knowing which persons are still in the building in case of emergency). The table has around 2 milion rows, we have around 3000 users.

The only solution I found was the one below, It works but the problem is it takes 15 seconds...

SELECT TIME, firstname, lastname, dept
FROM DAY
WHERE event_point_id IN (20, 22, 24, 26, 28, 30)
  AND id IN (SELECT MAX(id)
             FROM DAY
             GROUP BY pin)
GROUP BY pin
ORDER BY TIME ASC

DAY is a view with the following details

SELECT id, pin, event_point_id, time, firstname, lastname, dept
FROM acc_monitor_log
WHERE (((TO_DAYS(NOW()) - TO_DAYS(time)) < 1) AND (pin <> '--'))

What could be the options to speed up this?

Answer 1

A first suggestion is for reduce the IN clause using an inner join

SELECT TIME,firstname,lastname,dept
  FROM DAY
  INNER JOIN (
    SELECT pin, MAX(id) max_id 
    FROM DAY
    GROUP BY pin)
  ) T on t.max_id = DAY.id and t.pin = day.pin
  WHERE event_point_id IN (20,22,24,26,28,30)
  ORDER BY TIME ASC

or

SELECT TIME,firstname,lastname,dept
  FROM DAY
  INNER JOIN (
    SELECT pin, MAX(id) max_id 
    FROM DAY
    GROUP BY pin)
  ) T on t.max_id = DAY.id and t.pin = day.pin
  INNER JOIN (
    select 20 as my_point from dual
    union 
    select 22 from dual
     union 
    select 24 from dual       
    union 
    select 26 from dual
    union 
    select 28 from dual  
    union 
    select 30 from dual      
  ) t2  on t2.my_point  = DAY.event_point
  ORDER BY TIME ASC

and be sure you have proper index .. on column (pin, id) and also you could try add a proper table fo the value 20....30 with index instead of IN clause or subquery and use a join on this table

Answer 2

If I understand correctly, your DAY view is already filtering entries for the last 24 hours and what you want is to get only the most recent row for each user from that view.

If that's right, something straightforward like this should do the trick, and should be quicker than the IN in my opinion.

SELECT MAX(TIME), pin, firstname, lastname, dept
FROM DAY
WHERE event_point_id IN (20, 22, 24, 26, 28, 30)
GROUP BY pin, firstname, lastname, dept
ORDER BY TIME ASC
Answer 3

First, I would use a correlated subquery and get rid of the GROUP BY:

SELECT d.TIME, d.firstname, d.lastname, d.dept
FROM DAY d
WHERE d.event_point_id IN (20, 22, 24, 26, 28, 30) AND
      d.id = (SELECT MAX(d2.id)
              FROM Day d2
              WHERE d2.pin = d.pin
             )
ORDER BY TIME ASC;

Then, be sure you have indexes on the underlying table acc_monitor_log(pin, id) and acc_monitor_log(event_point_id, pin, id).

Rent Charter Buses Company
READ ALSO
Sort By Case: Where value LIKE, then POSITION

Sort By Case: Where value LIKE, then POSITION

I have a result set that needs to be sorted accordingly:

143
What does IsDBNull(int) method do?

What does IsDBNull(int) method do?

I have many users in a MySQL table in a WinForms appThe user enters their username and password

127
MySQl query term abbreviation

MySQl query term abbreviation

I want to collect two different tablodas from different valuesThis process takes too long because the number of records is too large How can I speed up the query?

126
Queries in query set group in to one query using Django

Queries in query set group in to one query using Django

I have a query set like the below

143