[MDEV-27678] Slow SELECT with two joins + where on 3rd table + group on 1st table date Created: 2022-01-29  Updated: 2022-01-30

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Fix Version/s: None

Type: Task Priority: Major
Reporter: Rafał Miłecki Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None

Attachments: File setup.sql    

 Description   

I noticed that one of my SELECT queries takes ~18 seconds. I started debugging it and prepared a minimal test case: a pretty simple SELECT over 3 tables (1500 rows in total) with 1 WHERE and 1 GROUP BY. That minimal SELECT still takes 10+ seconds.

I think there may be some bug in SELECT optimization. I'm not
export thought to fully understand the issue. I thought someone may want to take a look at it and consider optimizing MariaDB for such queries.

SELECT date
 
FROM aaa
 
LEFT JOIN bbb ON (bbb.code = aaa.code)
 
LEFT JOIN ccc ON (ccc.name = bbb.name)
 
WHERE ccc.type = "foo"
 
GROUP BY date;

On MariaDB 10.4.22 used by my hosting provider it takes 13 seconds to execute about query.

 Comments   
Comment by Rafał Miłecki [ 2022-01-29 ] 

{
 
  "query_block": {
 
    "select_id": 1,
 
    "r_loops": 1,
 
    "r_total_time_ms": 13344,
 
    "filesort": {
 
      "sort_key": "aaa.`date`",
 
      "r_loops": 1,
 
      "r_total_time_ms": 0.0476,
 
      "r_used_priority_queue": false,
 
      "r_output_rows": 349,
 
      "r_buffer_size": "7Kb",
 
      "temporary_table": {
 
        "table": {
 
          "table_name": "aaa",
 
          "access_type": "ALL",
 
          "r_loops": 1,
 
          "rows": 360,
 
          "r_rows": 360,
 
          "r_total_time_ms": 0.1158,
 
          "filtered": 100,
 
          "r_filtered": 100
 
        },
 
        "block-nl-join": {
 
          "table": {
 
            "table_name": "ccc",
 
            "access_type": "ALL",
 
            "r_loops": 1,
 
            "rows": 580,
 
            "r_rows": 580,
 
            "r_total_time_ms": 0.2298,
 
            "filtered": 100,
 
            "r_filtered": 96.552,
 
            "attached_condition": "ccc.`type` = 'foo'"
 
          },
 
          "buffer_type": "flat",
 
          "buffer_size": "5Kb",
 
          "join_type": "BNL",
 
          "r_filtered": 100
 
        },
 
        "block-nl-join": {
 
          "table": {
 
            "table_name": "bbb",
 
            "access_type": "ALL",
 
            "r_loops": 14,
 
            "rows": 602,
 
            "r_rows": 602,
 
            "r_total_time_ms": 4.7856,
 
            "filtered": 100,
 
            "r_filtered": 100
 
          },
 
          "buffer_type": "incremental",
 
          "buffer_size": "256Kb",
 
          "join_type": "BNL",
 
          "attached_condition": "bbb.`name` = ccc.`name` and bbb.`code` = aaa.`code`",
 
          "r_filtered": 2.9e-4
 
        }
 
      }
 
    }
 
  }
 
}

Comment by Rafał Miłecki [ 2022-01-30 ]

Workaround I found:

SELECT date
 
FROM (
 
	SELECT ccc.type, date
 
	FROM aaa
 
	LEFT JOIN bbb ON (bbb.code = aaa.code)
 
	LEFT JOIN ccc ON (ccc.name = bbb.name)
 
	GROUP BY ccc.type, date
 
) AS whatever
 
WHERE type = "foo";

Takes 0.05 s to execure

Generated at Thu Feb 08 09:54:44 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.