Reading Execution Plans

This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 执行计划的阅读

| username: MrSylar

An execution plan is shown above. I would like to ask how to understand the “range:[200,200], [300,300], (400 ‘2024-02-01’,400 ‘2024-05-01’]” here?

| username: 小龙虾爱大龙虾 | Original post link

Constructed predicate condition range

| username: forever | Original post link

Your OR condition is just multiple intervals, and within the OR, the equality interval exam and the end are the same.

| username: zhaokede | Original post link

Matches your WHERE condition.

| username: 濱崎悟空 | Original post link

The corresponding OR filter in the query

| username: 有猫万事足 | Original post link

The bolded conditions are: where (id=200 and lname=‘sylar’) or (id=300 and lname=‘john’) or (id=400 and lname=‘foller’) and hired>‘2024-02-01’ and hired<=‘2024-05-01’;

The reason why there is no lname-related content is because

table: employees, index: idx_id_hired_store(id, hired, store_id)

The idx_id_hired_store index under the employees table does not contain the lname field.
Therefore, lname-related filtering is handled in the subsequent selection operator.

| username: 友利奈绪 | Original post link

  1. “range:[200,200]”: This indicates that TiDB is using a range query during execution, where the values fall within the closed interval [200, 200]. This likely means the query needs to retrieve rows with a specific value, such as rows with a primary key of 200.
  2. “[300,300]”: Similar to the first item, this is also a range query, but this time the range is [300, 300], meaning rows with a value of 300.
  3. “(400 “2024-02-01”,400 “2024-05-01”]”: This is a time range query. In TiDB, time ranges may appear in the form of specific time points or date ranges. In this example, the query selects rows within the time range from “2024-02-01” (inclusive) to “2024-05-01” (exclusive), which is a half-open interval. This means TiDB will retrieve all data with timestamps between “2024-02-01” and “2024-05-01”.