April 18th, 2012, 05:19 PM
Can this self-join be optimized?
here's the query:
from sensor_data sd1
left join sensor_data sd2 on
sd1.sensor_id = sd2.sensor_id and
sd1.sequence_id < sd2.sequence_id
where sd1.sensor_status = 1
group by sd1.sensor_id, sd1.sequence_id
the sensor_data table has 4 columns:
[PK]sensor_id (varchar), sensor_status (int), sensor_time (datetime), [PK]sequence_id (int)
The purpose of the query is to essentially join each row with it's own next row per sensor_id. The size of the table is about 360,000. how can I make this more efficient?
April 18th, 2012, 05:38 PM
One thing you can look at is the indexes.
What does it say if you run a EXPLAIN on the query?
April 19th, 2012, 11:05 AM
There is a primary key on sensor_id, sequence_id; and another index on sensor_time. I'm actually cheating a bit because I'm on SQL server rather than MySQL, but I suspect that from a theoretical standpoint I can learn better things from the MySQL crowd.
Originally Posted by MrFujin
April 19th, 2012, 12:14 PM
It is best to post it where it really belongs to.
MSSQL and MySQL does not support the same functionality or support them the same way.
But to my knowledge, 360000 are not that many rows.
How long does the query takes in completion?
You can try to create an Excecution Plan and see how it get the result.
Maybe adding a index at sensor_status can make it faster.
April 19th, 2012, 03:03 PM
you are wrong, sir
Originally Posted by manzellb
if you get responses tailored to mysql and they don't work in sql server, the person who spent time on it for you -- for free -- is going to get pissed off
i'm moving your thread to the sql server forum