Can this self-join be optimized?

here's the query:

select sd1.sensor_id,
max(sd1.sensor_status) sensor_status,
max(sd1.sensor_time) start_time,
sd1.sequence_id start_sequence_id,
MIN(sd2.sensor_time) end_time,
MIN(sd2.sequence_id) end_sequence_id
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?

One thing you can look at is the indexes.

What does it say if you run a EXPLAIN on the query?

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.

Thanks!

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.

you are wrong, sir

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