Strange Slow Full Table Scan

Translated
1

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

Original topic: 奇怪的慢查全表扫描

| username: TiDB_C罗

[TiDB Usage Environment] Production Environment / Testing / Poc
[TiDB Version]
[Reproduction Path] What operations were performed to encounter the issue
[Encountered Issue: Problem Phenomenon and Impact]
[Resource Configuration]

select * from db1.account where id in (select
case when JSON_EXTRACT(response,'$.status') = 'OK' then JSON_EXTRACT(response,'$.result.one_face_with_multi_cards[0].user_id') 
else ' ' end   as concat_top_max_user_id 
from db1.face where account_id = 1000000
and option_type = 2
order by id desc limit 1)

The execution plan causes a full table scan on db1.account.
db1.account.id, decimal(20,0) BINARY) → Column#65, a forced conversion is clearly happening.
If you first retrieve the result and then execute select * from db1.account where id (xxxx), it uses Batch_Point_Get_1.
Then, modify the subquery to cast(case …when… end as unsigned).

image
image1164×508 16.6 KB

Using convert yields the same result.
Finally, perform the cast conversion in the case branch, resulting in:

cast(case when JSON_EXTRACT(response,'$.status') = 'OK' then cast(JSON_EXTRACT(response,'$.result.one_face_with_multi_cards[0].user_id') as  unsigned)
else '' end as unsigned)

The final statement is:

select * from db1.account where id in (select
cast(case when JSON_EXTRACT(response,'$.status') = 'OK' then cast(JSON_EXTRACT(response,'$.result.one_face_with_multi_cards[0].user_id') as  unsigned)
else '' end as unsigned)  as concat_top_max_user_id 
from db1.face where account_id = 1000000
and option_type = 2
order by id desc limit 1)

Finally solved it. Why are two cast conversions needed?

2
| username: RenlySir | Original post link

Because then and else might correspond to two different data types, they both need to be cast, right?

3
| username: tidb菜鸟一只 | Original post link

Because else ‘’ is a string.

4
| username: Jellybean | Original post link

The syntax of case when has the following two types:

1 CASE WHEN [expr] THEN [result1]…
  ELSE [default] END

2 CASE [col_name] WHEN [value1/expr1] THEN [result1]…
  ELSE [default] END

Your SQL uses two casts mainly to ensure the data format from case when is consistent:
SELECT * FROM db1.account WHERE id IN
(SELECT cast(
CASE WHEN JSON_EXTRACT(response,‘$.status’) = ‘OK’ THEN cast(JSON_EXTRACT(response,‘$.result.one_face_with_multi_cards[0].user_id’) AS unsigned)
ELSE ‘’ # The second cast is to escape the empty string here. If you set a default unsigned type user_id here, the second cast can be removed
END
AS unsigned) AS concat_top_max_user_id
FROM db1.face
WHERE account_id = 1000000 AND option_type = 2
ORDER BY id DESC
limit 1)

5
| username: redgame | Original post link

Well, the matter here with else ’ '.

6
| username: TiDB_C罗 | Original post link

Sure enough, each “when … then …” needs to be cast separately, and the outermost layer does not need to be cast again. Everyone’s answers are very correct, the best answer goes to the first reply. :grinning:

7
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.