Hi there,
we are having severe performance problems in our matomo installation (4.2), possibly after upgrading from 3.x, but that is not sure.
When a user logs in, it seems like there are several select statements searching in the matomo_log_action table but not using the hashed index for name.
With phpMyAdmin I can see that those SELECT’s are searching in 30 000 000 rows of matomo_log_action and they return about 7 000 rows with name “audi” in for example. This search takes about 100 seconds, the CPU Load goes to 100%.
This is the SELECT-statement, which causes the high load:
SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT(’%’, ‘audi-’, ‘%’) AND type = 4 )
=> Result is 7000 statements like this:
39194899
44399276
22335681
If I execute the statement by hand with the name-tag added to the statement ike this:
SELECT idaction,name FROM matomo_log_action WHERE ( name LIKE CONCAT(’%’, ‘audi-’, ‘%’) AND type = 4 )
it yields:
39194899 audi -#- Praktikum - Audi Customer Care der Zukunft (m/w/d) -#- 1301635 -#- r1gg53a63tcok7n340e6q9n935
44399276 audi -#- Praktikum - Audi Vorseriencenter (VSC) - Produkt- / Prozesstechnik Elektronik -#- 1040527 -#- m5uca6e97klb9rk7fvmmsen587
22335681 audi -#- Praktikum - Produktplanung Karosserie Lack (m/w/d) -#- 1281078 -#- b08eboq6lukpsmklilubtnvdb4
Takes also 100 seconds.
So far this is understandable for me but why takes this search place at all?
This statement originates from core/Tracker/TableLogAction.php and is built in the function
getSelectQueryWhereNameContains($matchType, $actionType)
and does not use the hashed index for name, obviously because there are wildcards in the name involved.
It seems like this has something to do with segmentation?
Any ideas on this problem would be appreciated…
Cheers Michael