Table indexes - performance enhancement

[vipsoft]

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

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

[vipsoft]

(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

[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

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.

[vipsoft]

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

[vipsoft]

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

[matt]

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?

[matt]

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)

[matt]

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.

[vipsoft]

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

[vipsoft]

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

[vipsoft]

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

[matt]

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

[vipsoft]

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