Operator_info: Will eq(eq(ifnull(broker.b_tr_info.ch_lic_no, ""), "abc")) use an index?

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

Original topic: operator_info: 为eq(eq(ifnull(broker.b_tr_info.ch_lic_no, “”), “abc”))会走索引吗

| username: 大飞飞呀

[TiDB Usage Environment] Production Environment
[TiDB Version]
[Reproduction Path] What operations were performed when the issue occurred
[Encountered Issue: Issue Phenomenon and Impact]
Step 1: Create a view
CREATE VIEW v AS
select * from (
select
ifnull(d.LIC_NO,‘’) LIC_NO,

                  from  a 
                    
                           left join   d on a.I_RE = d.I_BI
              ) w

a.I_RE d.I_BI d.LIC_NO all have indexes

Step 2: Query the view
explain select * from v where LIC_NO=‘abc’
Result
operator_info is
eq(ifnull(d.lic_no, “”), “abc”)

May I ask if the above query SQL index is effective?

[Resource Configuration]
[Attachment: Screenshot/Log/Monitoring]

| username: Billmay表妹 | Original post link

You can take a look at this practice~

| username: TiDBer_xt | Original post link

The WHERE condition in the query uses the LIC_NO column from the view v, but the LIC_NO column in the view is not indexed. Therefore, the index is not effective, and the query may be relatively slow.

| username: Billmay表妹 | Original post link

Based on the information you provided, it can be seen that the LIC_NO field in the query statement is queried from the view v, and the LIC_NO field in the view v is queried from the table d. According to the information you provided, the LIC_NO field in table d has an index, so the index can be used when executing the query statement.

After executing the EXPLAIN command, you can see eq(ifnull(d.lic_no, ""), "abc") in the operator_info, which indicates that the LIC_NO field in the query statement uses an equality query, meaning the index can be used. Therefore, it can be determined that the index in the query statement is effective.

| username: 大飞飞呀 | Original post link

How can I add an index to a view, brother?

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

Table A can use the index, but Table D cannot.

| username: ljluestc | Original post link

Based on the provided SQL statement and explanation output, the query optimizer seems to have generated an execution plan that uses the equality operator (eq) to compare the LIC_NO column in the view v with the string value “abc”.

Query optimizers typically use indexes to optimize queries that involve filtering rows based on specific values (such as equality operators). However, the presence of an index on a column does not necessarily guarantee that it will be used by the query optimizer.

To check if the query optimizer is using the index on the d.LIC_NO column, you can use the EXPLAIN statement with the ANALYZE keyword as follows:

EXPLAIN ANALYZE select * from v where LIC_NO='abc';

The ANALYZE keyword instructs TiDB to execute the query and collect runtime statistics, which can provide more information about how the query is executed.

The output of the EXPLAIN ANALYZE statement should include information about the query execution plan and any indexes used. Look for a row containing the Extra keyword, which can provide additional information about the execution plan.

If the index on the d.LIC_NO column is being used, the Extra keyword should include the text Using index or Using index condition. If the index is not being used, the Extra keyword may contain other information about how the query is executed, such as a full table scan.

It is also worth noting that the presence of an index on the d.LIC_NO column does not necessarily mean it is the most efficient index for that query. The query optimizer will evaluate all available indexes and choose the most efficient one based on various factors, such as the size and distribution of the data.