@anonymous-matomo-user opened this Issue on November 12th 2009

If all entries of the piwik_log_action.name field look like this:


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

@mattab commented on November 12th 2009 Member

please try on trunk, this was fixed with #708

This Issue was closed on November 12th 2009
Powered by GitHub Issue Mirror