[#17679] - Fix issue with mysql restriction max_join_size=300000
- Fixed in Code Base
- 27 Aug 2017
- Medium
- Build: staging
- # 17679
- Diff
- csthomas:issue_17580
User tests: Successful: Unsuccessful:
Pull Request for Issue #17580
Summary of Changes
Divide sql query into two in order to generate lighter queries.
Testing Instructions
Take a look at issue #17580
Documentation Changes Required
No
Votes
joomla-cms-bot
- | Category | ⇒ | Libraries |
| Status | New | ⇒ | Pending |
question (i have not tested) if you use a subquery at the where clause ?
I do not see an option for that.
Below is an example from my testing database (I'm updating 1 menu item with 4 children and one grandchild, total menu items are 769).
Old query structure:
EXPLAIN UPDATE `#__menu` AS c
INNER JOIN (
SELECT c2.`id` AS newId,CASE WHEN MIN(p2.`published`) > 0 THEN MAX(p2.`published`) ELSE MIN(p2.`published`) END AS newPublished
FROM `#__menu` AS node
INNER JOIN `#__menu` AS c2 ON node.lft <= c2.lft AND c2.rgt <= node.rgt
INNER JOIN `#__menu` AS p2 ON p2.lft <= c2.lft AND c2.rgt <= p2.rgt
WHERE node.`id` = 396
GROUP BY c2.`id`) AS c2 ON c2.newId = c.`id`
SET `published` = c2.newPublished| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | UPDATE | c | NULL | ALL | PRIMARY | NULL | NULL | NULL | 695 | 100.00 | NULL | |
| 1 | PRIMARY | NULL | ref | <auto_key0> | <auto_key0> | 4 | db.c.id | 25 | 100.00 | NULL | ||
| 2 | DERIVED | node | NULL | const | PRIMARY,idx_left_right | PRIMARY | 4 | const | 1 | 100.00 | NULL | |
| 2 | DERIVED | c2 | NULL | index | PRIMARY,idx_client_id_parent_id_alias_language,idx_componentid,idx_menutype,idx_left_right,idx_langu... | PRIMARY | 4 | NULL | 695 | 33.33 | Using where | |
| 2 | DERIVED | p2 | NULL | ALL | idx_left_right | NULL | NULL | NULL | 695 | 11.11 | Range checked for each record (index map: 0x10) |
My calculation for join_size is 695 * 25 * 1 * (695 / 3) * (695 / 9)
New query structure:
EXPLAIN UPDATE `#__menu` AS c
INNER JOIN (
SELECT c2.`id` AS newId,CASE WHEN MIN(p2.`published`) > 0 THEN MAX(p2.`published`) ELSE MIN(p2.`published`) END AS newPublished
FROM `#__menu` AS c2
INNER JOIN `#__menu` AS p2 ON p2.lft <= c2.lft AND c2.rgt <= p2.rgt
WHERE c2.`id` IN (396,882,1689,883,1155,1156)
GROUP BY c2.`id`) AS c2 ON c2.newId = c.`id`
SET `published` = c2.newPublished
WHERE c.`id` IN (396,882,1689,883,1155,1156)| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | UPDATE | c | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 6 | 100.00 | Using where | |
| 1 | PRIMARY | NULL | ref | <auto_key0> | <auto_key0> | 4 | db.c.id | 10 | 100.00 | NULL | ||
| 2 | DERIVED | c2 | NULL | range | PRIMARY,idx_client_id_parent_id_alias_language,idx_componentid,idx_menutype,idx_left_right,idx_langu... | PRIMARY | 4 | NULL | 6 | 100.00 | Using where | |
| 2 | DERIVED | p2 | NULL | ALL | idx_left_right | NULL | NULL | NULL | 695 | 11.11 | Range checked for each record (index map: 0x10) |
My calculation for join_size is 6 * 10 * 6 * (695 / 9) (only second query)
I have tested this item
This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/17679.
I have tested this item
Done code review too
Please note this fix for the SQL of unpublishing nested records is inside NestedTable class,
so it is a fix not only for MENU items only, but for every case of nested records, e.g. categories
but arguably the menu items is usually the most common case with many nested records ...
This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/17679.
| Status | Pending | ⇒ | Ready to Commit |
RTC after two successful tests.
thank you folks for your interest and testing
mbabker
- | Status | Ready to Commit | ⇒ | Fixed in Code Base |
| Closed_Date | 0000-00-00 00:00:00 | ⇒ | 2017-08-27 16:07:18 |
| Closed_By | ⇒ | mbabker | |
| Labels |
Added:
?
|
||
Nice, going
from Table X Table X Table
to Table X Table
from O(n*n*n) rows to O(n*n) rows
no wonder the join row limit was reached ...
question (i have not tested) if you use a subquery at the where clause ?
it should work the same and offer a small further improvement ?