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)