3

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 :)

Improve this question
2
  • What version MYSQL? Newest version has window functions. Commented Mar 19, 2019 at 7:41
  • @ypercubeᵀᴹ I think window funcs usage is excess on that task... the dates are set directly. Commented Mar 19, 2019 at 7:52

1 Answer 1

Reset to default
3 
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.

Improve this answer
2
  • It works great! Before I accept, what column should I put index on to optimize the query speed? Commented Mar 19, 2019 at 9:40
  • @Acidon what column should I put index on to optimize the query speed? It is difficult to answer. If items count is not too large maybe the index by date is enough, else the index by (date, item) may be more suitable. If record size is large (there is a lot of non-shown fields) maybe the index by (date, item, likes) will be the best. Test in practice. Commented Mar 19, 2019 at 10:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.