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