How to optimize my union all query

Question

0.00/5 (No votes)

See more:

Hello,

I have a mysql table that stores data about the presence of a sensor at each timestamp, in multiple areas.
If I have for example 3 areas, for a certain timestamp, my table will look like this:

timestamp    areas_id    is_in_or_out
 ts1             25          1
 ts1             26          0
 ts1             27          0
 ts2             25          1
 ts2             26          0
 ts2             27          0
 ts3             25          0
 ts3             26          0
 ts3             27          1

I am using the below query for each areas_id with union all in order to output in a single table, time periods of how my sensor traveled between areas and how much it stayed in/out each area.

SQL

select t.a_id, min(t.timestamp) starttime,max(t.timestamp) endtime, 
t.is_flag from(SELECT *,
ROW_NUMBER() OVER(ORDER BY a.timestamp) - ROW_NUMBER() OVER(PARTITION BY 
a.is_flag ORDER BY a.timestamp) as GRP
FROM tablename a where areas_id=25 ) t
group by is_flag , GRP, a_id

The desired output:

starttime     endtime      areas_id      is_in_or_out
 ts1           ts2           25             1
 ts1           ts3           26             0
 ts1           ts2           27             0
 ts3           tsx           27             1

Here is my dbfiddle: DBFIDDLE
Everything works fine, except the execution time for a whole day.
Is there another way of doing this faster? Perhaps a faster querry?

Thank you.

What I have tried:

Here is my dbfiddle: DBFIDDLE
My query does what I need, but it takes to long for a whole day.

Posted 29-Aug-20 22:47pm

Member 11166599

Updated 30-Aug-20 22:34pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2020-08-30T22:34:00

Sorry, but the description of issue is not quite clear, because:
1) if you want to get min and max values for timestamp field, you don't need to write several subqueries (for each areas_id); the group by statement is doing it already,
2) if you want to get continuos time for each areas_id, then you need to read this: The SQL of Gaps and Islands in Sequences - Simple Talk[^]

I'd strngly recommend to use correct data type for date and time. Instead of varchar(21), use datetime or timestamp. See: MySQL :: MySQL 8.0 Reference Manual :: 11.2.2 The DATE, DATETIME, and TIMESTAMP Types[^]

I tried below query:

SQL

SELECT areas_id, MIN(timestamp) starttime, MAX(timestamp) endtime, is_in_or_out
FROM inouts
GROUP BY areas_id, is_in_or_out;

The result set is the same as yours, but the execution time is two times shorter. For such of small portion of data the difference is big!

Good luck!