phpmyadmin Question posted in
The official documentation can be found here.

Why sql is loading data infinite? – Phpmyadmin

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

enter image description here

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

enter image description here

Now here is data I am displaying
enter image description here

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?

Tags:

3

Answers


  1. 0

    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 ;
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
  3. 0

    I think you can try to modify the configuration file of phpMyAdmin or Apache or Nginx. Actually, you should use the terminal to do statistics.

    By the way, you should make sure the configurations are correct:

    1. MySQL’s max_execution_time, just execute this SQL: SHOW VARIABLES LIKE '%max_execution_time%'

    2. Nginx: add keepalive_timeout 60s; to your nginx.conf

    3. PHP: modify max_execution_time value in php.ini

    4. phpMyAdmin: you can see this article

    There are too many places that cause timeouts.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search