Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-30022

Lateral Derived optimization is not applied when it should

    XMLWordPrintable

Details

    Description

      Consider this testcase:

      create table user (
        user_id int primary key,
        user_name varchar(32)
      );
       
      insert into user select 
      seq, concat('user-', seq) from seq_1_to_10000;
       
      create table shipping_address(
        user_id int primary key,
        address text
      );
       
      insert into shipping_address select 
      seq, concat('Address ', seq) from seq_1_to_10000;
       
      create view user_info as
      select U.user_id, user_name, address
      from 
        user U
        left join shipping_address ADDR on U.user_id=ADDR.user_id;
       
      create table orders(
        order_id int primary key,
        user_id int,
        amount double,
        key(user_id)
      );
       
      insert into orders select 
        seq, seq/100, 123 from seq_1_to_20000;
       
       
      create view user_info2 as 
      select U.user_id, user_name, ORD_TOTAL
      from
        user U
        left join (select
                     user_id, sum(amount) as ORD_TOTAL
                   from orders
                   group by user_id
                  ) ORD_TOTALS
           on ORD_TOTALS.user_id=U.user_id;
       
      

      Then:

      MariaDB [test]> explain select user_name, ORD_TOTAL from user_info2 where user_id in (1,2);
      +------+-----------------+------------+-------+---------------+---------+---------+----------------+------+-------------+
      | id   | select_type     | table      | type  | possible_keys | key     | key_len | ref            | rows | Extra       |
      +------+-----------------+------------+-------+---------------+---------+---------+----------------+------+-------------+
      |    1 | PRIMARY         | u          | range | PRIMARY       | PRIMARY | 4       | NULL           | 2    | Using where |
      |    1 | PRIMARY         | <derived3> | ref   | key0          | key0    | 5       | test.u.user_id | 4    |             |
      |    3 | LATERAL DERIVED | orders     | ref   | user_id       | user_id | 5       | test.u.user_id | 49   |             |
      +------+-----------------+------------+-------+---------------+---------+---------+----------------+------+-------------+
      

      Good.
      But what if we just want the data for user_id=1?
      A much more expensive plan is used:

      MariaDB [test]> explain select user_name, ORD_TOTAL from user_info2 where user_id=1;
      +------+-------------+------------+-------+---------------+---------+---------+-------+-------+-------------+
      | id   | select_type | table      | type  | possible_keys | key     | key_len | ref   | rows  | Extra       |
      +------+-------------+------------+-------+---------------+---------+---------+-------+-------+-------------+
      |    1 | PRIMARY     | u          | const | PRIMARY       | PRIMARY | 4       | const | 1     |             |
      |    1 | PRIMARY     | <derived3> | ALL   | NULL          | NULL    | NULL    | NULL  | 19982 | Using where |
      |    3 | DERIVED     | orders     | index | NULL          | user_id | 5       | NULL  | 19982 |             |
      +------+-------------+------------+-------+---------------+---------+---------+-------+-------+-------------+
      3 rows in set (0.001 sec)
      

      Attachments

        Activity

          People

            Unassigned Unassigned
            psergei Sergei Petrunia
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.