Opened 3 years ago

Closed 3 years ago

#1780 closed Task (fixed)

Missing index in mysql table piwik_options

Reported by: PeterPan Owned by: matt
Priority: low Milestone: Piwik 1.2
Component: Core Keywords:
Cc: Sensitive: no

Description

Hey guys,

nothing to worry that bad about, nevertheless, in my mysql log the query

SELECT option_value, option_name FROM piwik_option WHERE autoload = 1;

appeared. This query does not use an index on the column autoload. As it contains not that much stuff the performance issue is not that dramatic but it gives spaces for optimisation at this point.
Or have there been any special reasons which I did not consider, yet?

rgrds
Peter

Change History (7)

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

Is there a MySQL message?

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

  • Milestone set to Features requests 1.x or 2.x

Nevermind. I found the setting: http://dev.mysql.com/doc/refman/5.0/en/server-options.html#option_mysqld_log-queries-not-using-indexes

matt: add an index for autoload, option name, or both?

comment:3 Changed 3 years ago by PeterPan

Well in this specific case i just checked with this one parameter autoload for selection and setting an additional indey for this column.
Maybe its worth a check to do an explain select on this query with both types of indexes and an in case the index of both fields does fine, too just measure the qeuery speed.

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

  • Milestone changed from Features requests 1.x or 2.x to 1.1 - Piwik 1.1

comment:5 Changed 3 years ago by matt (mattab)

  • Milestone changed from 1.1 - Piwik 1.1 to 1.2 - Piwik 1.2

the primary key is option name already. I think it makes sense to add an index on autoload, to help the query autoloading the core _option values.

Given how _option is used in product, storing settings for websites and users, this suggestion makes sense.

comment:6 Changed 3 years ago by matt (mattab)

  • Owner set to matt

comment:7 Changed 3 years ago by matt (mattab)

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

(In [3696]) Page titles report improvements fixes #1898

  • new fields in 2 log tables (major schema upgrade in next release!)
  • code refactored and optimized, (only the UI to display top entry/exit page titles is missing)

Performance improvements

  • Actions Archiving is much more efficient, removed many JOINs and updating algorithm so that we select and parse action names only once per action.
  • Fixes #1600: datatables now indexed by int, no data migration but code works with both old and new data structure
  • Fixes #1780: new index

Improvements to integration tests

  • never loads the Provider plugin in proxy-piwik.php since reverse ip lookup slows up tests a lot
  • fixing a test result that were previously incorrect (_withCookieSupport) because a static cache wasn't cleaned after each test
Note: See TracTickets for help on using tickets.