I have a query that ran perfectly fine up until 2 days ago. It queries a remote database getting quiz data for a couple thousand students. Normally the result set is around 10000 rows.
2 days ago, the script started hanging and I've tracked it down to this query:
mysql_query("SELECT qg.userid, ROUND( qg.grade * 100 / q.grade, 2 ) grade, q.grade as qgrade, q.id quizid, qg.timemodified FROM mdl_quiz_grades qg, mdl_quiz q WHERE q.id = qg.quiz AND q.course = 47 And q.id in (-1,153,158,163,170,176,181,184,199,205,206) Order By userid, quizid
limiting results to 3000 is fine, and it executes immediately, but if limited to 3001 it hangs without finishing.
Also I can execute the query in phpMyAdmin with no problem.
Edit: The results limit has been changing. I was just able to get a return of 3700 results and now can no longer.
Edit2: More testing in phpMyAdmin. When accessing records from the 3000+ range, phpMyAdmin will hang and eventually throw a 500 internal server error.
Edit3: Well. testing is suspended. The Remote Database has just started using SSL for database connections, so my previous user isnt working. I now need to configure my client for SSL. I'm guessing they were making adjustments through the week that was screwing with my script.
Two possibilities:
Are you able to fetch at least 1 record?
Try increasing the max_execution_time in your php.ini file
Change memory limit to 128M if it is low
This is your query:
SELECT qg.userid, ROUND( qg.grade * 100 / q.grade, 2 ) as grade,
q.grade as qgrade,
q.id as quizid, qg.timemodified
FROM mdl_quiz_grades qg JOIN
mdl_quiz q
ON q.id = qg.quiz
WHERE q.course = 47 And
q.id in (-1, 153, 158, 163, 170, 176, 181, 184, 199, 205, 206)
Order By qg.userid, quizid;
I do not know why your performance is suffering. However, the above query will run faster with the correct indexes. The best indexes are: mdl_quiz(course, id, grade) and mld_quiz_grades(quiz).