Details
-
Type:
Bug
-
Status:
Resolved
-
Priority:
Critical
-
Resolution: Can't Fix
-
Affects Version/s: 1.2.2
-
Fix Version/s: None
-
Component/s: Query
-
Labels:None
-
Environment:LAMP - other details seem irrelevant
Description
Doctrine seems to be adding DISTINCT to SQL queries without any good reason (to the table's primary key!) and thus SERIOUSLY HURTS performance. It even seems like Doctrine adds a whole new DISTINCT query without a good reason. I believe the extra query is the result of the different implementation of LIMIT in Doctrine vs. SQL. However, when a unique index is involved, this extra query seems redundant.
In the following example, execution time went from ~5sec down to ~1sec when I simply removed the DISTINCT added by Doctrine.
Why is the extra query created anyway?
Is there any way to prevent Doctrine to add the DISTINCT part when there's a unique index on the column?
Here's the DQL:
SELECT i.id, ip.item_id, pic.id, pic.width, pic.height, i.producer_id, i.series_id, i.issue_date, t_i.name, FROM ZpcPhonecard i INNER JOIN i.Picture pic WITH pic.width > 125 AND pic.width >= pic.height LEFT JOIN i.Translation t_i WITH t_i.lang = 0 INDEXBY t_i.lang WHERE i.front_picture_id > 0 AND i.state = ? AND i.zpc_system_id = 4 ORDER BY i.id DESC LIMIT 20
And the resulting SQLs:
SELECT DISTINCT z3.id FROM zpc_phonecard z3 INNER JOIN picture p2 ON z3.front_picture_id = p2.id AND ((p2.width > 125 AND p2.width >= p2.height)) LEFT JOIN zpc_phonecard_translation z4 ON z3.id = z4.id AND (z4.lang = 0) WHERE (z3.front_picture_id > 0 AND z3.state = 'Active') AND z3.zpc_system_id = 4 ORDER BY z3.id DESC LIMIT 20;
SELECT z.id AS z_id, z.producer_id AS zproducer_id, z.series_id AS zseries_id, z.issue_date AS zissue_date, p.id AS pid, p.width AS pwidth, p.height AS pheight, z2.id AS z2id, z2.lang AS z2lang, z2.name AS z2_name FROM zpc_phonecard z INNER JOIN picture p ON z.front_picture_id = p.id AND ((p.width > 125 AND p.width >= p.height)) LEFT JOIN zpc_phonecard_translation z2 ON z.id = z2.id AND (z2.lang = 0) WHERE z.id IN ('231871', '231870', '231869', '231868', '231865', '231864', '231863', '231862', '231861', '231860', '231859', '231858', '231857', '231856', '231855', '231853', '231852', '231851', '231850', '231849') AND ((z.front_picture_id > 0 AND z.state = 'Active') AND z.zpc_system_id = 4) ORDER BY z.id DESC
Here's my own translation from DQL to A MUCH FASTER SINGLE SQL statement:
SELECT i.id, pic.id, pic.width, pic.height, i.producer_id, i.series_id, i.issue_date, t_i.name FROM Zpc_Phonecard i INNER JOIN Picture pic ON i.front_picture_id=pic.id AND pic.width > 125 AND pic.width >= pic.height LEFT JOIN Zpc_Phonecard_translation t_i ON i.id = t_i.id AND t_i.lang = 0 WHERE i.front_picture_id > 0 AND i.state = 'Active' AND i.zpc_system_id = 4 ORDER BY i.id DESC LIMIT 20
Here's a workaround to avoid Doctrine's handling of LIMIT queries. Is this there recommended way or is there another?
Before the $q->execute() part simply add these lines:
$q->buildSqlQuery(false);
$map = $q->getRootDeclaration();
$table = $map['table'];
$table->setAttribute(Doctrine_Core::ATTR_QUERY_LIMIT, Doctrine_Core::LIMIT_ROWS);
Now the DQL is automatically translated to the following SQL:
SELECT z.id AS z_id, z.producer_id AS zproducer_id, z.series_id AS zseries_id, z.issue_date AS zissue_date, p.id AS pid, p.width AS pwidth, p.height AS pheight, z2.id AS z2id, z2.lang AS z2lang, z2.name AS z2_name FROM zpc_phonecard z INNER JOIN picture p ON z.front_picture_id = p.id AND ((p.width > 125 AND p.width >= p.height)) LEFT JOIN zpc_phonecard_translation z2 ON z.id = z2.id AND (z2.lang = 0) WHERE ((z.front_picture_id > 0 AND z.state = 'Active') AND z.zpc_system_id = 4) ORDER BY z.id DESC LIMIT 20