Subtract values in same column from 2 different dates in same Mysql table

Question

I have a MySql table like:

Item        Date          Likes

Book        2012-05-20    402
Book        2012-05-21    432
Book        2012-05-22    450
Pen         2012-05-20    20
Pen         2012-05-21    20
Pen         2012-05-22    25

1) What is the simplest query for calculating and returning the difference in Likes column between 2 set dates for all of the Item values, ordered by the Difference?

For example:

Likes difference between 2012-05-20 and 2012-05-21 returns next results:

Item        Difference
Book        30
Pen         0

Likes difference between 2012-05-20 and 2012-05-22 returns next results:

Item        Difference
Book        48
Pen         5

2) Also there will be some cases when there will be no records for some of the dates for an Item in the table, in those cases no results for that Item should be returned.

3) What should be the correct indices to optimize the speed of the query?

I did come across this query that might work but it is a huge query and I am sure that there can be a more compact and elegant solution :)

Answer 1

SELECT t1.item, t2.likes - t1.likes difference
FROM table t1, table t2
WHERE t1.item = t2.item
  AND t1.date = @first_date
  AND t2.date = @second_date

If you need all items (ever the data on @first_date or @second_date is not present), rewrite from cartesian to FULL JOIN and use COALESCE to replace NULLs with zeros.