Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: 执行计划
I don’t quite understand the execution plan part. Why is the index on table b not used? Table m has 394 rows, and table b has 7397 rows.
SQL text: EXPLAIN ANALYZE SELECT
m.
,
b.
FROM
PLC_RISKMAINCONFIG m,
PLC_RISKDETAILCOFIG b
WHERE
1 = 1
AND m.id = b.parentId
AND m.coeffCategoryTwoCode = ‘1’
AND m.viewFlag = ‘0’
AND b.intervalBig < ‘1’ AND b.intervalSmall > ‘1’
AND productcode = ‘27010001’
The image is not available for translation. Please provide the text content directly.
The conditions for Table B are not in the index, right?
AND b.intervalBig < ‘1’ AND b.intervalSmall > ‘1’
I see that it is the b and m tables being joined. Why can’t you use the join condition m.id = b.parentId?
You can refer to this for understanding:
Are the types of the two fields consistent?
They are different. One is decimal and the other is varchar. In this case, the index cannot be used, right?
There will be implicit conversion, causing the index to be unusable.
So how do you convert this kind of thing to be the same?
b.interbig is used as the access method for table b, so indexes are not used. The associated column is converted back to the table and type after the condition. It is impossible to use indexes, right?
Converting ‘1’ to 1 removes the single quotes and recognizes it as a number. Adding single quotes makes it a character. For you, both are 1, but for the machine, the types are different.
|
username: 胡杨树旁
|
username: 
|
username: 
|
username: