Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP

Loading…

Table indexes - performance enhancement #1129

Closed
robocoder opened this Issue · 12 comments

2 participants

@robocoder
Collaborator

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

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

@robocoder
Collaborator

(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
Collaborator

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
Collaborator

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

@robocoder
Collaborator

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

@mattab
Owner

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
Owner

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
Owner

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
Collaborator

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
Collaborator

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

@robocoder
Collaborator

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

@mattab
Owner

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

@robocoder
Collaborator

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

@robocoder robocoder added this to the Piwik 0.5.5 milestone
This issue was closed.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.