Going to the customers page in BO takes about 30 minutes! :)

[alexmixaylov]

Hello gus!

Now I have mate a situation 

My customer`s store (1.6) has a problem

When he wants going to /index.php?controller=AdminCustomers page it take about 30 min (I gave this result on my local server, on the production server it itakes about 5minute with cache and without debug)

I turned on debug mode and DEBUG_PROFILING and there is result

sql> SELECT SQL_NO_CACHE a.`id_customer`, `firstname`, `lastname`, `email`, a.`active` AS `active`, `newsletter`, `optin`
             ,
                         a.date_add, gl.name as title, (
                             SELECT SUM(total_paid_real / conversion_rate)
                             FROM ps_orders o
                             WHERE o.id_customer = a.id_customer
                               AND o.id_shop IN (1)
                               AND o.valid = 1
                         ) as total_spent, (
                             SELECT c.date_add FROM ps_guest g
                                                        LEFT JOIN ps_connections c ON c.id_guest = g.id_guest
                             WHERE g.id_customer = a.id_customer
                             ORDER BY c.date_add DESC
                             LIMIT 1
                         ) as connect
     FROM `ps_customer` a
              LEFT JOIN ps_gender_lang gl ON (a.id_gender = gl.id_gender AND gl.id_lang = 1)
     WHERE 1  AND a.`deleted` = 0
     ORDER BY `date_add` DESC  LIMIT 0, 50
[2019-11-25 14:46:57] 50 rows retrieved starting from 1 in 29 m 45 s 441 ms (execution: 29 m 45 s 66 ms, fetching: 375 ms)

this is request from  /classes/controller/AdminController.php:3330 (screen below)

it takes 186778 ms and   touches on Rows  :))))) 

Although the return result only 50 rows.

when I tryied to make raw sql request from console - same sutiation 

anybody can help me?

 

PrestaShop version: 1.6.1.6

Server information: Linux #1 SMP Debian 4.19.67-2+deb10u2 (2019-11-11) x86_64

Server software version: Apache/2.4.38 (Debian)

PHP version: 7.1.33-1+0~20191026.27+debian10~1.gbpd10139

Memory limit: 256M


MySQL version: 10.2.23-MariaDB-10.2.23+maria~stretch

MySQL server: localhost

MySQL name: stopshop

MySQL user: root

Tables prefix: ps_

MySQL engine: InnoDB

MySQL driver: DbPDO

 

[juanrojas]

Try updating to the latest version of 1.6 in your localhost and see if it solves the problem, I also recommend that you deactivate the module that connects with the addons

[alexmixaylov]

I have found the problem

It was because in tables ps_guests and ps_connection was too many rows - more than 5 000 000

I cleaned it and it solve my problem