0
Hi,
I have a page with lots of data (5GB of DB) and Zoo intensive which works fine with other components, but since some months ago I cannot access Content Statistics. In another posts (Error 500) you requested access to the site but I never got a reply after that.
Can you please let me know how I can proceed to fix this issue?
Thanks!!
I have a page with lots of data (5GB of DB) and Zoo intensive which works fine with other components, but since some months ago I cannot access Content Statistics. In another posts (Error 500) you requested access to the site but I never got a reply after that.
Can you please let me know how I can proceed to fix this issue?
Thanks!!
Responses (9)
-
Accepted Answer
-
Accepted Answer
0Hi 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.-
Germinal Campsmore than a month agohmm.. it's weird that this could crash the system. getTotal is just a count on the query.
how many rows (approx) are there in this table?
-
-
Accepted Answer
0ok
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? -
Accepted Answer
0> 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. -
Accepted Answer
0Germinal 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. -
Accepted Answer
0Hi 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!! -
Accepted Answer
-
Accepted Answer
0Regarding 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!!
Your Reply

Please login to post a reply
You will need to be logged in to be able to post a reply. Login using the form on the right or register an account if you are new here.
Register Here »