php - 为什么sql无限加载数据？

Question

我有两个用于统计事件和 session 的表，这是它们的外观

此处 session 表显示第 0 - 29 行(总共 4 730 018 行))



这是事件表显示第 0 - 29 行(共 3686 行)



现在这是我正在显示的数据


现在这是我用于显示数据的 SQL

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

现在当我在 session 表中有大量数据时，你可以看到超过 400 万条数据，

并尝试在我的 PHPmyadmin 中运行上面的脚本，请求永远不会结束，MariaDB 不再响应，我必须手动重新启动服务。

我需要做什么来解决我的问题？

Answer 1

我怀疑问题出在 count(distinct) .我也认为引入 sid 没有用或 datetime .

这是否做你想做的

 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 ;