Dear all,
y server has been optimized and run stable, but I found this kind of query usualy take a lot row scan in my DB. And also take a lot CPU.
Since the frequency was not too high, but this could be escalated to 0,6 req/s for some case.
I ve searched in my code but could not found the keyword DISTINCTROW in any file, could you share me where the cause?
Thank very much
SELECT `u` . `id` FROM `mdl_user` `u` JOIN ( SELECT DISTINCTROW `eu2_u` . `id` FROM `mdl_user` `eu2_u` WHERE ? = ? AND `eu2_u` . `deleted` = ? ) `je` ON `je` . `id` = `u` . `id` WHERE `u` . `deleted` = ? ORDER BY `u` . `lastname` , `u` . `firstname` , `u` . `id`
Making that more readable:
SELECT u.id
FROM mdl_user u
JOIN (
SELECT DISTINCTROW eu2_u.id
FROM mdl_user eu2_u
WHERE ? = ? AND eu2_udeleted = ?
) je ON je.id = u.id
WHERE u.deleted = ?
ORDER BY u.lastname, u.firstname, u.id
The use of a subquery is silly there. This could be a flat join - I wonder where in the Moodle code this query is coming from? There is mention of DISTINCTROW in the standard Moodle code, which makes me suspect a plugin.
I've searched in my code base as well, but not found.
I think it may comes from some query builder, like report!
Any help?
I think it may comes from some query builder, like report!
Any help?
The cause is from Badge plugin.
Please optimize this, so our earth would be greener ;)
Please optimize this, so our earth would be greener ;)
In reply to Hung Phan
Re: Trả lời: Re: A strange query consumes a lot row and join
by Séverin Terrier -
Hi Hung,
If you could specify when (doing what) exactly this query is launched, that would certainly help developpers to know what/where to look at to optimize.
Séverin
If you could specify when (doing what) exactly this query is launched, that would certainly help developpers to know what/where to look at to optimize.
Séverin