Sql query interrupted by server for a table with over 1m records?

Question

I have a website created in Magento with approx 1120 products in there.

When I select the bulit-in Magento menu option: Reports -> Products -> Most Viewed, then enter in a date range and select "Show By" = [Day], I get a timeout error. But the SAME report but where "Show By" = [Month] and "Show By" = [Year], runs to completion and the report is generated.

Upon investigation, I found table [Report_Event] has in excess of 1 million records. When I tried to run the SAME query in PHPMyAdmin, I get message: "SQL query was interrupted by server".

Has anybody come across this error? Is there a workaround or a setting that needs to be applied?

Answer 1

Try to increase max_execution_time of your server or script. To do this for server - change corresponding parameter in php.ini. To do this in your script use ini_set(max_execution_time,$amount) where $amount is allowed time for script to be executed in seconds. If you want to test how long it takes use ini_set(max_execution_time,0). There is a couple of good answers here - On using ini_set('max_execution_time', 0)

As for Magento try to place ini_set(max_execution_time,$amount) at the beginning of index.php.