search

matomo-org / matomo

Empowering People Ethically with the leading open source alternative to Google Analytics that gives you full control over your data. Matomo lets you easily collect data from websites & apps and visualise this data and extract insights. Privacy is built-in. Liberating Web Analytics. Star us on Github? +1. And we love Pull Requests!
https://matomo.org/
GNU General Public License v3.0
19.59k stars 2.61k forks source link

Wrong Index choosen by MySQL #4740

Open anonymous-matomo-user opened 10 years ago

anonymous-matomo-user commented 10 years ago

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 10 years ago

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-matomo-user commented 10 years ago

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.