Optimize DbNestedSetStorage::findParent

[larowlan]

Problem statement

Fetching a parent on a large table can result in a filesort or temporary table

before:

explain SELECT parent.id, parent.revision_id, parent.left_pos, parent.right_pos, parent.depth FROM nested_set_field_parent_node AS child, nested_set_field_parent_node AS parent WHERE child.left_pos BETWEEN parent.left_pos AND parent.right_pos AND child.id = '90736' AND child.revision_id = '6389766' ORDER BY parent.left_pos\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: child
   partitions: NULL
         type: const
possible_keys: PRIMARY,IDX_E1907B87BF3967501DFA7C8F5E183DDBDEADD74FAA31C69,IDX_E1907B875E183DDBDEADD74,IDX_E1907B875E183DD,depth__left__vid,left__depth
          key: PRIMARY
      key_len: 8
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: parent
   partitions: NULL
         type: index
possible_keys: IDX_E1907B875E183DDBDEADD74,IDX_E1907B87BDEADD74,IDX_E1907B875E183DD,left__depth
          key: IDX_E1907B87BF3967501DFA7C8F5E183DDBDEADD74FAA31C69
      key_len: 20
          ref: NULL
         rows: 131378
     filtered: 25.00
        Extra: Using where; Using index
2 rows in set, 1 warning (0.00 sec)

Proposed resolution

Rewrite `::findParent to order by parent.left_pos DESC and add limit 1

after:

explain SELECT parent.id, parent.revision_id, parent.left_pos, parent.right_pos, parent.depth FROM nested_set_field_parent_node AS child, nested_set_field_parent_node AS parent WHERE child.left_pos BETWEEN parent.left_pos AND parent.right_pos AND child.id = '90736' AND child.revision_id = '6389766' ORDER BY parent.left_pos DESC limit 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: child
   partitions: NULL
         type: const
possible_keys: PRIMARY,IDX_E1907B87BF3967501DFA7C8F5E183DDBDEADD74FAA31C69,IDX_E1907B875E183DDBDEADD74,IDX_E1907B875E183DD,depth__left__vid,left__depth
          key: PRIMARY
      key_len: 8
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: parent
   partitions: NULL
         type: index
possible_keys: IDX_E1907B875E183DDBDEADD74,IDX_E1907B87BDEADD74,IDX_E1907B875E183DD,left__depth
          key: IDX_E1907B875E183DD
      key_len: 4
          ref: NULL
         rows: 4
     filtered: 25.00
        Extra: Using where
2 rows in set, 1 warning (0.00 sec)