@anonymous-piwik-user opened this Issue on February 23rd 2014

On my MySQL installation
5.5.31-0+wheezy1-log (Debian)

The query regarding the custom var aggregation are using thw wrong index.

Here the Explain
https://gist.github.com/RoyBellingan/9170698

The choosen index is a three column index
idsite
config_id
visit_last_action_time

If a "hint" to use the index_idsite_datetime (two column), a very good improvement can be seen.

https://gist.github.com/anonymous/9170625

@mattab commented on February 23rd 2014 Owner

I'm not sure if this is a bug. My guess is that you don't have enough rows in the table. I believe if you had more rows in your table, the query optimizer would pick the right index. Could you try run the same query on 10 or even 100 times more data?

@anonymous-piwik-user commented on February 23rd 2014

Is not a bug, just a performance improvement in reality...

The log_visit is around 1M rows

I've rerunned it now, increasing the time span
log_visit.visit_last_action_time >= '2014-02-10 23:00:00'
AND log_visit.visit_last_action_time <= '2014-02-24 22:59:59'

Explain again suggest to use index_idsite_config_datetime, this time rows count is around 540K.

Powered by GitHub Issue Mirror