[RFC] Add monthly stats

[HLeithner]

Since our stats are not really useful to get up to date information about Joomla usage I would suggest to generate the following reports.

Monthly php/cms/db Version (maybe db_type and server_os too) considering only the last 90 days. Generating this in real time is too time consuming (at least with my tests but a history should be available anyway).

I thought about something similar to a table structure based on jos_jstats_content_db_type. Creating a new table jos_jstats_history_db_type and adding the year and the month should be enough (of course adding the right indexes).

Based on the work done by @alikon https://github.com/joomla/statistics-server/pull/43 (which actually doesn't work on our stats server because mysql is not able to handle 1,5m rows without a proper index and even with index it doesn't like to use it, adding a hint doesn't help either) we can create a cron job with an simple REPLACE query like REPLACE #__js_jstats_content_db_type (year, month, db_type, count) VALUES(YEAR(NOW()), MONTH(NOW()), '$sqltype', '$count') based on the result of something like this

SELECT db_type, count(*) as count FROM (
  SELECT * FROM `jos_jstats` use index(idx_modified) WHERE modified > DATE_SUB(NOW(), INTERVAL 90 DAY)
) tmptable group by db_type`

(using a temp table here because mysql doesn't like to use the index, I have to check the reason for this maybe it's the group by clause or someone else can enlighten me)

We can add the last 6 month or so on the stats https://developer.joomla.org/about/stats.html

Any comment is welcome.

[zero-24]

Sounds like a good plan to me. :+1:

[alikon]

is Redis available on that server ?

[HLeithner]

@alikon do we need it? I mean we need the history anyway and if we update a mysql server or but the data into redis doesn't make a big different especially if want to keep it anyway.

Btw. @richard67 helped me debugging your query and it seems mysql doesn't use index if the selected column is not part of the index. So it seems that the instant view is the best to do it but still to slow for real time and also not needed in my opinion.