Why sql is loading data infinite?

Question

I have two tables for statistics events, and sessions, here is how they look

Here Sessions Table Showing rows 0 - 29 (4 730 018 total))

And here is events table Showing rows 0 - 29 (3686 total)

Now here is data I am displaying

Now here is my SQL for displaying data

SELECT sessions.sid, 
       events.datetime, 
       count(*) as num_rows, 
       count(distinct sessions.sid) as sessions, 
    sum( targetbuttonname = 'kredyt' ) as num_kredyt, 
    sum( targetbuttonname = 'konto' ) as num_konto,
    sum( targetbuttonname = 'czat' ) as num_czat,
    sum( targetbuttonname = 'video-voice_btns' ) as num_voice,
    sum( targetbuttonname = 'video-close_btn' )  as num_close,
    sum( targetbuttonname = 'video-muted_btn' ) as num_muted,
    sum( targetbuttonname = 'video-play_btn' )  as num_play,
    sum( targetbuttonname = 'video-pause_btn' )  as num_pause,
    sum( targetbuttonname = 'video-replay_btn' ) as num_replay, 
    sum(watchtime) as num_watchtime, 
    sum(devicetype ='Computer') as num_computer 
from events INNER JOIN
     sessions
     ON events.sid =sessions.sid;

Now when I have small data everything works fine

Now when I have huge data in a sessions table as you can see above over 4 million data,

And try to run the script above in my PHPmyadmin the request never ends and MariaDB doesn't respond anymore, I have to restart the service manually.

What do I need to do to solve my problem?

Answer 1

I suspect the problem is the count(distinct). I also see no use in bringing in sid or datetime.

Does this do what you want

 select count(*) as num_rows,
        (select count(*) from sessions) as sessions,
        sum( targetbuttonname = 'kredyt' ) as num_kredyt, 
        sum( targetbuttonname = 'konto' ) as num_konto,
        sum( targetbuttonname = 'czat' ) as num_czat,
        sum( targetbuttonname = 'video-voice_btns' ) as num_voice,
        sum( targetbuttonname = 'video-close_btn' )  as num_close,
        sum( targetbuttonname = 'video-muted_btn' ) as num_muted,
        sum( targetbuttonname = 'video-play_btn' )  as num_play,
        sum( targetbuttonname = 'video-pause_btn' )  as num_pause,
        sum( targetbuttonname = 'video-replay_btn' ) as num_replay, 
        sum(watchtime) as num_watchtime, 
        sum(devicetype ='Computer') as num_computer 
 from events e ;

Answer 2

Since you said it works when there’s small amount of data, issue could be time out (fetching or transferring) data. Try limiting no of rows you get. You can use “limit by” to achieve this.