hi,

if you access phpMyAdmin, can you see how big is the content statistics table? (how many rows?)

Hi Germinal,
thank you very much for your reply.

The table is 575.7 MB big. Zoo Items table is around 1GB and it works with no issues in the querying.

From my little research on this I think the problem is you are using a direct query on "buildQuery" method:

$query = ' SELECT st.*, u.name as username '

		. ' FROM #__content_statistics as st '

		. ' LEFT JOIN #__users as u ON u.id = st.user_id ' 

		. $where_clause

		. $orderby

		;

And when that code comes to the getTotal method, it totally crashes the system (too big of a result).

Maybe simplifying it for the getTotal method, makes the query lighter and it improves performance.

It's exactly 5,486,347 rows.

Best!!

ok

this is A LOT of rows.

the only suggestion I have is to delete old data, so the table is not so big.

Probably you have some very old data in there. if you want to keep it, you can make a copy of the table, but I would try to keep the main table smaller (maybe only data from the last year or last few months)

do you know how to do that?

> hmm.. it's weird that this could crash the system. getTotal is just a count on the query.

Actually that's my point!! As you are using a direct query, the _getListCount() doesn't replace the items in the select, it just performs the full query on all the table!!

I have just tested it placing this code just before the return of the buildQuery method and the error dissappears:

$db = JFactory::getDbo();

$query = $db->getQuery(true);

$query->select('st.*, u.name as username');

$query->from('#__content_statistics as st');

$query->join('left', '#__users as u ON u.id = st.user_id');

$query->order('st.date_event', 'asc');



		return $query;

This code allows Joomla Model to replace the fields in the select with a proper "Count" function.

Germinal Camps wrote:

ok

this is A LOT of rows.

the only suggestion I have is to delete old data, so the table is not so big.

Probably you have some very old data in there. if you want to keep it, you can make a copy of the table, but I would try to keep the main table smaller (maybe only data from the last year or last few months)

do you know how to do that?

Sorry Germinal,
I saw your reply just after pushing mine. Can you please consider using that code in the component? I have tested it and it allows the dashboard to load properly. Actually you just need to plug the "Where" conditions there and your component will perform much better!!

We will check on removing old data but as we cannot access the component, there is not much we can do at the moment from the component so I would rather have this fixed than keep getting these errors.

Please let me know.

Hi Germinal,
did you read my messages and test my code? I also have some suggetions that will for sure improve a bit performance like:

1. Load a range of data on first load (like only last semester)
2. Automatically export yearly data (or any kind of periods)
3. Allow an unatended export for the data to be able to split the process and avoid max execution time and memory issues when creating the csv file or provide the sql query needed to get the data in a reasonable clean way directly from phpMyAdmin

Still waiting for a fix for this issue.

Best!!

hi!

thanks, yes I read it, I will look into it.

1. what you mean? always is never loaded "all at once"... it's always paginated...
2. good idea!
3. yep

Regarding 1. I mean setting by default a 1 year filter so that it doesn't need to count all rows in the table. As per my case, even with my modifications, if you set the "order by" clause, the sql query is too much and the server cannot handle it.

It's a simple way of avoiding a server overload issue to fetch data that to be honest I do not think people need to see at first glance

Do you have an ETA of when any of these changes will be available?

Best!!