#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    27
    Rep Power
    0

    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?
  2. #2
  3. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,706
    Rep Power
    1959
    One thing you can look at is the indexes.

    What does it say if you run a EXPLAIN on the query?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    27
    Rep Power
    0
    Originally Posted by MrFujin
    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!
  6. #4
  7. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,706
    Rep Power
    1959
    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.
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,262
    Rep Power
    4279
    Originally Posted by manzellb
    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.
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo