Prestashop: Slow query on ps_connections for a large scale prestashop

[lucmichalski]

Hi guys,

Hope you are doing all well !

I am writing this issue because I am facing a tremendously slow sql query made by Prestashop and my client does not want to truncate the tables as a solution. It slows down the performances of the backend awfully.

Please find below some useful informations:

Prestashop config

• Version 1.7.6.1
• PHP 7.3.25
• 16 cores / 60GB RAM

slow query

SELECT c.id_guest, c.ip_address, c.date_add, c.http_referer, "-" as page
FROM `ps_connections` c
INNER JOIN `ps_guest` g ON c.id_guest = g.id_guest
WHERE (g.id_customer IS NULL OR g.id_customer = 0)
    AND c.id_shop IN (2, 1) 
    AND TIME_TO_SEC(TIMEDIFF('2020-11-29 12:22:00', c.`date_add`)) < 900                    
ORDER BY c.date_add DESC

ps_connections table info

ps_guest table info

explained query

Ps. I renamed the tables with suffixes in order to work on my staging version of the e-shop.

How can we sort it that out ? Can you rewrite the query for avoiding to scan all the rows of the ps_connections table ?

Thanks for any insights or inputs about this issue.

Cheers,
Luc Michalski

[hibatallahAouadni]

Hello @lucmichalski

You need to downgrade your PHP version to 7.2 cause PrestaShop 1.7.6.1 is not compatible with PHP 7.3, please see our system requirements

Please check and feedback.

Thanks!

[lucmichalski]

@hibatallahAouadni

Thanks for your reply :-)

Can you advise on the sql query issue please ?

Cheers,
Luc

[hibatallahAouadni]

Hello @lucmichalski

Ps. I renamed the tables with suffixes in order to work on my staging version of the e-shop.
How can we sort it that out ? Can you rewrite the query for avoiding to scan all the rows of the ps_connections table ?

We need a developer to answer this :sweat_smile:
Ping @PrestaShop/prestashop-core-developers @PrestaShop/prestashop-maintainers could anyone, please, answer his question (mentioned above)

Thanks!

[matthieu-rolland]

Just one question: does it slow down the backend only in the BO's statistics pages, or in every pages of the BO ?
It seems related to the dashactivity and statslive modules.

[lucmichalski]

@matthieu-rolland

Thanks for your reply :-)

It takes even long for loading the module administration page (as initially I was thinking it was a module causing the slowness).
So the answer is yes for all BO pages.

Cheers,
Luc

[matthieu-rolland]

ok, I had a look at it and talked about it with a colleague that knows those modules.

Unfortunately, there is no quick fix for improving these modules, especially the statslive module. These modules are very old and have not been reworked for a long time. Improving it would take more than rewriting the SQL query, it would need some changes in the way data is stored and retrieved.

You can definitely do a feature request though because this is a legitimate problem that would need some solving. Then our product team will prioritize it and eventually, this will be dealt with by one of the developers maintaining the open-source project.

You can do an improvement request here:

https://github.com/PrestaShop/PrestaShop/issues/new?template=2_feature_request.md

For now, I don't see a quick solution except to disable those two modules, unfortunately... I don't close this issue right now in case someone would have a better idea I didn't think of.

[lucmichalski]

Thanks for your insights, does a limit to the sql query could improve the query time ?

[matthieu-rolland]

Thanks for your insights, does a limit to the sql query could improve the query time ?

Well this query is to get statistics about the shop's number of visits (it's the block on the left in the dashboard page, in the BO). Adding a limit would give a wrong result, if this data is not important for you, you could just as well disable the module.

[matteolavaggi]

Trow all the statistic module in a basket. They are usefull, old, buggy and ridicoulous slow. Use a third part metric system like analytics

[matthieu-rolland]

The problem with google analytics and other frontend analytics tools is that they are blocked by adblockers... Anyway I'll create an improvement request.