MySQL - How to simplify this query?

i have a query which i want to simplify:

select
      sequence,
      1 added
      from scoredtable
      where score_timestamp=1292239056000
      and sequence
      not in (select sequence from scoredtable where score_timestamp=1292238452000)
      union
select
      sequence,
      0 added
      from scoredtable
      where score_timestamp=1292238452000
      and sequence
      not in (select sequence from scoredtable where score_timestamp=1292239056000);

Any ideas? basically i want to extract from the same table all the sequences that are different betweent two timestamp values. With a colum "added" which represents if a row is new or if a row has been deleted.

Source table:

score_timestamp             sequence
1292239056000               0
1292239056000               1
1292239056000               2
1292238452000               1
1292238452000               2
1292238452000               开发者_JAVA百科3

Example between (1292239056000, 1292238452000) Query result (2 rows):

sequence added
3        1
0        0

Example between (1292238452000, 1292239056000) Query result (2 rows):

sequence added
0        1
3        0

Example between (1292239056000, 1292239056000) Query result (0 rows):

sequence added

This query gets all sequences that appear only once within both timestamps, and checks if it occurs for the first or for the second timestamp.

SELECT
  sequence,
  CASE WHEN MIN(score_timestamp) = 1292239056000 THEN 0 ELSE 1 END AS added
FROM scoredtable
WHERE score_timestamp IN ( 1292239056000, 1292238452000 )
  AND ( 1292239056000 <> 1292238452000 )   -- No rows, when timestamp is the same
GROUP BY sequence
HAVING COUNT(*) = 1

It returns your desired result:

sequence added
3       1
0       0

Given two timestamps

SET @ts1 := 1292239056000
SET @ts2 := 1292238452000

you can get your additions and deletes with:

SELECT s1.sequence AS sequence, 0 as added
FROM scoredtable s1 LEFT JOIN
     scoredtable s2 ON 
                       s2.score_timestamp = @ts2 AND
                       s1.sequence = s2.sequence
WHERE
     s1.score_timestamp = @ts1 AND
     s2.score_timestampe IS NULL
UNION ALL
SELECT s2.sequence, 1
FROM scoredtable s1 RIGHT JOIN
     scoredtable s2 ON s1.score_timestamp = @ts1 AND
                       s1.sequence = s2.sequence
WHERE
     s2.score_timestamp = @ts2 AND
     s1.score_timestampe IS NULL

depending on the number of rows and the statistics the above query might perform better then group by and having count(*) = 1 version (i think that will always need full table scan, while the above union should be able to do 2 x anti-join which might fare better)

If you have substantial data set, do let us know which is faster (test with SQL_NO_CACHE for comparable results)