fetching data from cumulative-sum table

Question

This is my main table which stores keyword impressions according to dates.

+---------+-----------+------------+-------------+
| ID      | KeywordId | ReportDate | Impressions |
+---------+-----------+------------+-------------+
| 3324620 | 969274443 | 2018-12-21 | 1           |
+---------+-----------+------------+-------------+
| 3334253 | 969274443 | 2018-12-22 | 0           |
+---------+-----------+------------+-------------+
| 3354119 | 969274443 | 2018-12-23 | 20          |
+---------+-----------+------------+-------------+
| 3373097 | 969274443 | 2018-12-24 | 11          |
+---------+-----------+------------+-------------+
| 3392168 | 969274443 | 2018-12-25 | 1           |
+---------+-----------+------------+-------------+

And this is my cumulative sum table which adds up impressions day by day

+----+-----------+------------+-------------+
| ID | keywordId | ReportDate | Impressions |
+----+-----------+------------+-------------+
| 1  | 969274443 | 2018-12-21 | 1           |
+----+-----------+------------+-------------+
| 2  | 969274443 | 2018-12-22 | 1           |
+----+-----------+------------+-------------+
| 3  | 969274443 | 2018-12-23 | 21          |
+----+-----------+------------+-------------+
| 4  | 969274443 | 2018-12-24 | 32          |
+----+-----------+------------+-------------+
| 5  | 969274443 | 2018-12-25 | 33          |
+----+-----------+------------+-------------+

to get the number of impressions between date 2018-12-21 and 2018-12-25, simply I subtract

33 - 1 and get the result 22.

I used the below query to get this result.

select  keyword.keywordId,(second.impressions - first.impressions) as imp from keyword
inner join diffreportkeyword as first on first.keywordId = keyword.keywordId
                                     and first.reportDate = '2018-12-21'
inner join diffreportkeyword as second on second.keywordId = keyword.keywordId
                                      and second.reportDate = '2018-12-25'

but sometimes, there is no data for a specific date and query returns 0 rows.But this means that for a specific date the impressions are 0.

If i change this with left join,

select  keyword.keywordId,(second.impressions - first.impressions) as imp from keyword
left join diffreportkeyword as first on first.keywordId = keyword.keywordId
                                     and first.reportDate = '2018-12-18'
left join diffreportkeyword as second on second.keywordId = keyword.keywordId
                                      and second.reportDate = '2018-12-25'

this works, but diffreportkeyword table has 5million rows and the keyword table has 300k different "keywordId" and left join makes the query extremely slow.

My question is,

how can I make it faster?

thanks

Please read https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-on-so-when-asking-a-question and replace images with text. — nbk, Apr 22 '20 at 20:29
how can I add a sample table here instead of an image ? @nbk — Burc Hasergin, Apr 23 '20 at 13:27
Which programm are you using, [phpmyadmin](https://www.codekrypt.com/export-website-data-using-phpmyadmin/) or [mysql workbench](https://dev.mysql.com/doc/workbench/en/wb-admin-export-import-results.html) and check @Strawbberrys link so that the query will worl — nbk, Apr 23 '20 at 14:43
@Strawberry you are refering me to your comment but I exactly don't understand what should I do more ? — Burc Hasergin, Apr 25 '20 at 09:35
With which part of the accepted answer at the linked question are you struggling? — Strawberry, Apr 25 '20 at 09:48
I added a sample dataset and and the query as mentioned in the the accepted answer.Thats why i have no idea what to write here more ?I an new to here so if you can tell me the problem i can handle it @Strawberry — Burc Hasergin, Apr 25 '20 at 12:16
Any comment I make here will only repeat what's already said there, so I just can't see the point of doing that. — Strawberry, Apr 25 '20 at 12:30
@Strawberry,just tell me what is missing so i will add it to question?because in my opinion I wrote all the neccessary information here. — Burc Hasergin, Apr 26 '20 at 09:43

score 0 · Answer 1 · answered Apr 26 '20 at 22:38

0

This

  on second.keywordId = keyword.keywordId
 and second.reportDate = '2018-12-25'

needs

INDEX(keywordId, reportDate)  -- in either order

Side note: It might be simpler to say

    '2018-12-21' - INTERVAL 3 DAY

instead of

    '2018-12-18'

answered Apr 26 '20 at 22:38

Rick James

135,179
13
127
222

fetching data from cumulative-sum table

1 Answers1