Too small index results in massive performance issue when identifying part of the entries in piwik_log_action.name starts after position 15 #1036

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

please try on trunk, this was fixed with #708