Version: 2.11.1 Reporter: iainsaxon —
This is an archived bug report. If you are experiencing a similar issue, upgrade to the latest release and if that does not solve the problem, submit a new bug report
I noticed this issue in the latest version of EE 2.11.2 and when I checked back with the site running an earlier EE 2.10.3 build it was still a problem.
When accessing a page containing an EE Relationship the page load time was a massive 136 seconds (worst case recorded locally was ~180 seconds) to render the page and without caching active takes 50 seconds on our live site.
The simplified EE template code looks like this:
{exp:channel:entries
channel="companies"
limit="100"
status="open|closed"
paginate="inline"
orderby="title"
sort="asc"
}
{parents channel="departments" field="cf_department_company"}
...
{/parents}
{/exp:channel:entries}After investigating the issue I found that the performance issue was caused by an unoptimised MySQL query:
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, `L0`.`order`
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`.`child_id` IN (11440, 11441, 11442, 11443, 11444, 11445, 11446, 11447, 11448, 11449, 11450, 11451, 11452, 11453, 11454, 11455, 11456, 11457, 11458, 11459, 11460, 11461, 11462, 11463, 11464, 11465, 11466, 11467, 11468, 11469, 11470, 11471, 11472, 11473, 11474, 11475, 11476, 11477, 11478, 11479, 11480, 11481, 11482, 11483, 11484, 11485, 11486, 11487, 11488, 11489, 11490, 11491, 11492, 11493, 11494, 11495, 11496, 11497, 11498, 11499, 11500, 11501, 11502, 11503, 11504, 11505, 11506, 11507, 11508, 11509, 11510, 11511, 11512, 11513, 11514, 11515, 11516, 11517, 11518, 11519, 11520, 11521, 11522, 11523, 11524, 11525, 11526, 11527, 11528, 11529, 11530, 11531, 1, 11532, 11533, 11534, 11535, 11536, 11537, 11538, 11539, 11540, 11541, 11542, 11543, 11544, 11545, 11546, 11547, 11548, 11549, 11550, 11551, 11552, 11553, 11554, 11555, 13728, 11556, 11557, 11558, 11559, 11560, 11561, 11562, 11563, 11564, 11565, 11566, 11567, 11568, 11569, 11570, 11571, 11572, 11573, 11574, 11575, 11576, 11577, 11578, 11579, 11580, 11581, 11582, 11583, 11584, 11585, 11586, 11587, 11588, 11589, 11590, 11591)
ORDER BY `L0`.`order` ascThe query return 3989 rows taking 136 seconds.
An `EXPLAIN EXTENDED` had this to say about the query:
"id","select_type","table","partitions","type","possible_keys","key","key_len","ref","rows","filtered","Extra"
1,"SIMPLE","L0",NULL,"ref","child_id,grid_field_id","grid_field_id",4,"const",8450,10.61,"Using index condition; Using where; Using temporary; Using filesort"
1,"SIMPLE","L1",NULL,"ALL","parent_id,child_id",NULL,NULL,NULL,64718,100.00,"Range checked for each record (index map: 0x6); Distinct"It seems that the LEFT JOIN’s `OR L1.child_id = NULL` is the issue here as the query speeds up dramatically without it and returns the same 3989 results in 71 ms:
"id","select_type","table","partitions","type","possible_keys","key","key_len","ref","rows","filtered","Extra"
1,"SIMPLE","L0",NULL,"ref","child_id,grid_field_id","grid_field_id",4,"const",8450,10.61,"Using index condition; Using where; Using temporary; Using filesort"
1,"SIMPLE","L1",NULL,"ref","parent_id","parent_id",4,"mydatabase.L0.parent_id",16,100.00,"Using index; Distinct"PHP versions are 5.6.19 locally and 5.3.24 on the live site and MySQL 5.7.11 locally and 5.5.35-33.0 on the live database.
It seems that the second part of the LEFT JOIN condition is what causes the query optimiser problems and results in very slow query speeds.
Does the query need the `OR L1.child_id = NULL` on the join query or is there another way to formulate this query to make it perform better?
ExpressionEngine implements Markdown Extra and BBCode. Please see the Markdown Extra docs and the BBCode Wikipedia article for a full reference.
**bold**, __bold__, *italics*, _italics_, ~strike/del~, `code()`
bold, italics, strike/del, code()
Link: [link title](https://example.com)
Image: 
[blockquote]...[/blockquote], [quote]...[/quote], and Markdown style:
> Some quoted text. > > This is all one quote.
[code]...[/code], and you can also specify the language for syntax highlighting, [code=php]...[/code]
GitHub flavored Markdown code fences are also supported:
```
public function decoderRing($str)
{
return str_rot13($str);
}
```