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

Table indexes - performance enhancement #1129

Closed
robocoder opened this issue Jan 28, 2010 · 12 comments
Closed

Table indexes - performance enhancement #1129

robocoder opened this issue Jan 28, 2010 · 12 comments
Labels
Bug For errors / faults / flaws / inconsistencies etc. Major Indicates the severity or impact or benefit of an issue is much higher than normal but not critical.
Milestone

Comments

@robocoder
Copy link
Contributor

See: http://forum.piwik.org/index.php?showtopic=4491

Patch from Maciej re: comment:ticket:386:14

@robocoder
Copy link
Contributor Author

(In [1807]) fixes #1129 - tracker performance enhancement - add md5_config(8) to index_idsite on log_visit; add index_period_archived to archive tables; replace index_all with index_idsite_dates_period

@robocoder
Copy link
Contributor Author

EXPLAIN with index_all shows the key_len as 708; ref is null

EXPLAIN with index_idsite_dates_period shows the key_len as 15; ref is const,const,const,const

If I add ts_archived to the new index, EXPLAIN shows the key_len as 15 still. This suggests adding ts_archived is not beneficial, perhaps because the query contains a WHERE clause with ts_archived >= condition.

@robocoder
Copy link
Contributor Author

(In [1811]) refs #1129 - add update script

@robocoder
Copy link
Contributor Author

(In [1831]) fixes #1129 - change non-primary/unique key to use INDEX alias instead of KEY keyword

@mattab
Copy link
Member

mattab commented Feb 11, 2010

Replying to vipsoft:

EXPLAIN with index_all shows the key_len as 708; ref is null

EXPLAIN with index_idsite_dates_period shows the key_len as 15; ref is const,const,const,const

Vipsoft, which query did you EXPLAIN?

@mattab
Copy link
Member

mattab commented Feb 11, 2010

I guess you EXPLAINed the DELETE query?

Reopening: the name part of the index was removed but it is useful as you might have hundreds of name for a (idsite, date1,date2,period) tuple. Looking at the query in isArchived() in core/ArchiveProcessing.php it looks like the index should be on (idsite,date1,date2,period,name)

@mattab
Copy link
Member

mattab commented Feb 11, 2010

Also, the new index structure on the archive_ tables should be upgraded in the update for 0.5.5 so that all Piwik instances are kept consistent. Two main reasons: performance for all users, and if one day we delete the INDEX for a new one, this would not throw errors on installs missing this INDEX.

@robocoder
Copy link
Contributor Author

Replying to matt:

No, I EXPLAINed the SELECT.

When I EXPLAIN with both index_idsite_dates_period and index_idsite_dates_period_name, only index_idsite_dates_period is used. With only index_idsite_dates_period_name, EXPLAIN shows ref=null. My decision to exclude name is based on MySQL's apparent preference and the storage consideration. Perhaps the query should be split into two variants -- blob vs numeric.

As for retroactive updates, of course, we can do this. (But I didn't see this done when Piwik went from BLOB to MEDIUMBLOB.)

@robocoder
Copy link
Contributor Author

(In [1844]) refs #1129, refs #1151 - refactoring to add getSql() to return array of SQL statements

@robocoder
Copy link
Contributor Author

[1844] also applies the index changes retroactively to existing archive tables, to the update script

@mattab
Copy link
Member

mattab commented Feb 19, 2010

Anthon, can this ticket be closed? are we happy with the current status of INDEXes on the archive_* and log_* tables?

@robocoder
Copy link
Contributor Author

(In [1852]) fixes #1129 - remove index_idsite_dates_period (formerly index_all) from archive_blob as it isn't used in any queries

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. 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