You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
BugFor errors / faults / flaws / inconsistencies etc.duplicateFor issues that already existed in our issue tracker and were reported previously.MajorIndicates the severity or impact or benefit of an issue is much higher than normal but not critical.
then the index "index_type_name", which is defined as:
KEY index_type_name (type, name(15))
has a cardinality of 1 (which means that a full table scan is neccessary for every single SELECT that looks for a name) because the name field is searched only for 'my/site/has/a/l' (position 15).
Yesterday, a friend of mine needed help with a server that ran Piwik only, for a mid-traffic site, and had a constant load of 90. The mysql process list consisted of dozens of queries like this one:
SELECT idaction FROM piwik_log_action WHERE name = 'my/site/has/a/long/uri/?s=af3729febc827382424' AND type = 3
I then applied the following changes:
DROP INDEX index_type_name ON piwik_log_action;
CREATE INDEX index_type_name ON piwik_log_action (type, name(50));
and since then, the server is running at an average load of 0.25, and the process list looks normal (that is, empty).
BugFor errors / faults / flaws / inconsistencies etc.duplicateFor issues that already existed in our issue tracker and were reported previously.MajorIndicates the severity or impact or benefit of an issue is much higher than normal but not critical.
If all entries of the piwik_log_action.name field look like this:
'my/site/has/a/long/uri/?s=af3729febc827382424'
'my/site/has/a/long/uri/?s=0ca629febb623893883'
'my/site/has/a/long/uri/?s=9ceff388edb34093490'
'my/site/has/a/long/uri/?s=5bbefef672beaa82839'
'my/site/has/a/long/uri/?s=55392fea00bccde0392'
then the index "index_type_name", which is defined as:
KEY index_type_name (type, name(15))
has a cardinality of 1 (which means that a full table scan is neccessary for every single SELECT that looks for a name) because the name field is searched only for 'my/site/has/a/l' (position 15).
Yesterday, a friend of mine needed help with a server that ran Piwik only, for a mid-traffic site, and had a constant load of 90. The mysql process list consisted of dozens of queries like this one:
SELECT idaction FROM piwik_log_action WHERE name = 'my/site/has/a/long/uri/?s=af3729febc827382424' AND type = 3
I then applied the following changes:
DROP INDEX index_type_name ON piwik_log_action;
CREATE INDEX index_type_name ON piwik_log_action (type, name(50));
and since then, the server is running at an average load of 0.25, and the process list looks normal (that is, empty).
Note this issue is related to ticket #708.
Keywords: performance index key slow too short index_type_name piwik_log_action load
The text was updated successfully, but these errors were encountered: