Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
anonymous-matomo-user opened this issue Nov 12, 2009 · 1 comment
Labels
Bug For errors / faults / flaws / inconsistencies etc. duplicate For issues that already existed in our issue tracker and were reported previously. Major Indicates the severity or impact or benefit of an issue is much higher than normal but not critical.

Comments

@anonymous-matomo-user
Copy link

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

@mattab
Copy link
Member

mattab commented Nov 12, 2009

please try on trunk, this was fixed with #708

This issue was closed.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Bug For errors / faults / flaws / inconsistencies etc. duplicate For issues that already existed in our issue tracker and were reported previously. Major Indicates the severity or impact or benefit of an issue is much higher than normal but not critical.
Projects
None yet
Development

No branches or pull requests

2 participants