Opened 4 years ago

Closed 4 years ago

#1129 closed Bug (fixed)

Table indexes - performance enhancement

Reported by: vipsoft Owned by:
Priority: major Milestone: Piwik 0.5.5
Component: Core Keywords:
Cc: Sensitive: no

Change History (15)

comment:1 Changed 4 years ago by vipsoft (robocoder)

  • Resolution set to fixed
  • Status changed from new to closed

(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

comment:2 follow-up: Changed 4 years ago by vipsoft (robocoder)

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.

comment:3 Changed 4 years ago by vipsoft (robocoder)

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

comment:4 Changed 4 years ago by vipsoft (robocoder)

  • Resolution fixed deleted
  • Status changed from closed to reopened

comment:5 Changed 4 years ago by vipsoft (robocoder)

  • Owner set to vipsoft
  • Status changed from reopened to new

comment:6 Changed 4 years ago by vipsoft (robocoder)

  • Resolution set to fixed
  • Status changed from new to closed

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

comment:7 in reply to: ↑ 2 Changed 4 years ago by matt (mattab)

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?

comment:8 follow-up: Changed 4 years ago by matt (mattab)

  • Resolution fixed deleted
  • Status changed from closed to reopened

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)

comment:9 Changed 4 years ago by matt (mattab)

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.

comment:10 in reply to: ↑ 8 Changed 4 years ago by vipsoft (robocoder)

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

comment:11 Changed 4 years ago by vipsoft (robocoder)

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

comment:12 Changed 4 years ago by vipsoft (robocoder)

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

comment:13 Changed 4 years ago by vipsoft (robocoder)

  • Owner vipsoft deleted
  • Status changed from reopened to new

comment:14 Changed 4 years ago by matt (mattab)

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

comment:15 Changed 4 years ago by vipsoft (robocoder)

  • Resolution set to fixed
  • Status changed from new to closed

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

Note: See TracTickets for help on using tickets.