I need help with the following query. I'm not sure where it comes from in ExpressionEngine. Any insight or pointers would be helpful.

SELECT DISTINCT `L0`.`field_id` as L0_field, `L0`.`grid_field_id` as L0_grid_field_id, `L0`.`grid_col_id` as L0_grid_col_id, `L0`.`grid_row_id` as L0_grid_row_id, `L0`.`child_id` AS L0_parent, `L0`.`parent_id` as L0_id
FROM (`exp_relationships` as L0)
LEFT JOIN `exp_relationships` as L1 ON `L0`.`parent_id` = `L1`.`parent_id` OR L1.child_id = NULL
WHERE `L0`.`grid_field_id` =  0
AND `L0`.`grid_field_id` =  0
AND `L0`.`child_id` IN (6)
ORDER BY `L0`.`order` asc\G

Specifically 'L1.child_id = NULL'

If we drop the noted constraint (which is non-functional anyways), the query completes in no time at all and uses indexing properly. With the invalid constraint, the query takes 0.33 seconds on an idle database server, and much much longer on a busy db.

  • Have you thought about to report this as a bug? – Sobral Jan 21 '15 at 0:54
  • Looks like it might be from line 113 - 122 of /system/expressionengine/models/relationship_model.php – AllInOne Jan 21 '15 at 14:37
  • L1 is a temporary table so there is no possibility of using an index in the WHERE clause that operates on it. – AllInOne Jan 21 '15 at 14:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.