Custom Variable Searching - Very Slow Query

Support & Bugs
1

Hi Guys,
Long time user, first time poster! We’re using the Piwik API VisitsSummary.getVisits with a date range and filtering by a custom variable. This seems to force Piwik to search through the entire raw log table (4million entries) which takes a very long time (55 seconds!!). Does anyone know how we might be able to optimize this?

Time: 120401 17:14:32

User@Host: user[host] @ [127.0.0.0]

Query_time: 55 Lock_time: 0 Rows_sent: 1 Rows_examined: 4704750

use piwik;
SELECT
count(distinct log_visit.idvisitor) as nb_uniq_visitors
FROM
piwik_log_visit AS log_visit
WHERE
( log_visit.visit_last_action_time >= ‘2012-03-01 00:00:00’
AND log_visit.visit_last_action_time <= ‘2012-03-31 23:59:59’
AND log_visit.idsite = ‘1’ )
AND
( log_visit.custom_var_k1 = ‘CKey’ AND log_visit.custom_var_v1 = ‘CVal’ );

(Using latest version, 1.7.1)

Any help is really appreciated!
-Sam

2

If you often query for the same segments, you can tell Piwik to pre-compute specific segments. See the feature in the config file: http://dev.piwik.org/trac/browser/trunk/config/global.ini.php#L358

If you use auto archiving this will pre-process the reports which will load fast. Not ideal, but works for some use cases

3

Thanks for the info Matt, we often query for the same custom variable key, but the value and date range we’re querying changes often. In the below it look as though you have to explicitly define the key and the value you want it to archive, is this the case?

360 [Segments]
361 ; Reports with segmentation in API requests are processed in real time.
362 ; On high traffic websites it is recommended to pre-process the data
363 ; so that the analytics reports are always fast to load.
364 ; You can define below the list of Segments strings
365 ; for which all reports should be Archived during the cron execution
366 ; All segment values MUST be URL encoded.
367 ;Segments=“visitorType==new”
368 ;Segments=“visitorType==returning”
369
370 ; If you define Custom Variables for your visitor, for example set the visit type
371 ;Segments=“customVariableName1==VisitType;customVariableValue1==Customer”

4

Yes that’s correct, because we need to know what to archive

5

[quote=matt]
Yes that’s correct, because we need to know what to archive[/quote]
Okay thats fair enough, is there no way to define it as:

Segments=“customVariableName1==VisitType;customVariableValue1==*”

Where * could be a wildcard? Forgive me if I’m missing something, I’m just trying to work out the best way to get it not to process the data in real-time.
Thanks again!

6

It would be nice if it was possible but it isn’t yet

7

Happy New Year! If you are still using Piwik and interested in the awesome new feature of “Creating a Custom Segment in Piwik and apply to reports in Real time!” we need YOUR help, with a little or big donation at: http://crowdfunding.piwik.org/custom-segments-editor/

This will allow to dynamically add or edit, a new set of rules for example “Show all visitors from USA and using Firefox and using Google”. This will be done via a simple to use interface. See screenshots and more info here: http://crowdfunding.piwik.org/custom-segments-editor/

We are crowd funding the future of Piwik and this feature in particular. With your help we can do it.

Thanks

Matthieu