TiDB indexes are not getting used for queries

Question

I'm using mysql connector to connect with TiDB. I have found that indexes are not getting used in my queries. After my analysis i have found that due to casting indexes were not used. E.g

CREATE TABLE Employee (
ID BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
EmpID VARCHAR(200) NOT NULL
);
CREATE INDEX idx_EmpID ON Employee(EmpID);
1.Explain SELECT * FROM Employee WHERE EmpID= 123;
2.Explain SELECT * FROM Employee WHERE EmpID= '123';

In above example if i run first query and check the execution plan using explain then no indexes are used however in second query as I'm providing quotes for varchar datatype index is getting used.

However this solution needs to be tested via .net mysqlconnector library. Currently I'm using mysql command parameters to execute queries.

1.Current Logic with AddWithValue function

                MySqlCommand cmd1 = new MySqlCommand
                {
                    CommandText = "Select * from Employee where EmpID = @EmpID"
                };

                cmd1.Parameters.AddWithValue($"@EmpID", 1234);

2.Updated logic by providing datatype for the field using Add function

                MySqlCommand cmd1 = new MySqlCommand
                {
                    CommandText = "Select * from Employee where EmpID = @EmpID"
                };

                cmd1.Parameters.Add($"@EmpID", MySqlDbType.VarChar).Value = 1234;

I want to know the query representation at server side when processing the request. Would the updated logic .i.e point 2 would form the query by providing quotes for varchar datatype?

score 0 · Answer 1 · answered Apr 16 '21 at 08:55

I think TiDB and MySQL have the same behaviour here. If you are comparing the VARCHAR column with a string that it compares it as a string and it will use the index. When comparing it with an integer then it will try to convert the value of each row to an integer and then compare it. Then it can't use the index. Note that you need to add a few rows to the table to get a more realisic explain plan. Also note that depending on the type the result can be different.

mysql 8.0.22 > CREATE TABLE Employee (
    -> ID BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> EmpID VARCHAR(200) NOT NULL
    -> );
Query OK, 0 rows affected (0.14 sec)

mysql 8.0.22 > CREATE INDEX idx_EmpID ON Employee(EmpID);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql 8.0.22 > EXPLAIN SELECT * FROM Employee WHERE EmpID= 123;
+----+-------------+----------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+----------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | Employee | NULL       | index | idx_EmpID     | idx_EmpID | 802     | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
1 row in set, 3 warnings (0.00 sec)

mysql 8.0.22 > EXPLAIN SELECT * FROM Employee WHERE EmpID= '123';
+----+-------------+----------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | Employee | NULL       | ref  | idx_EmpID     | idx_EmpID | 802     | const |    1 |   100.00 | Using index |
+----+-------------+----------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql 8.0.22 > INSERT INTO Employee(EmpID) VALUES ('010'),('10');
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql 8.0.22 > SELECT * FROM Employee;
+----+-------+
| ID | EmpID |
+----+-------+
|  1 | 010   |
|  2 | 10    |
+----+-------+
2 rows in set (0.00 sec)

mysql 8.0.22 > SELECT * FROM Employee WHERE EmpID='10';
+----+-------+
| ID | EmpID |
+----+-------+
|  2 | 10    |
+----+-------+
1 row in set (0.00 sec)

mysql 8.0.22 > SELECT * FROM Employee WHERE EmpID=10;
+----+-------+
| ID | EmpID |
+----+-------+
|  1 | 010   |
|  2 | 10    |
+----+-------+
2 rows in set (0.00 sec)

tidb 5.7.25-TiDB-v5.0.0 > CREATE TABLE Employee (
    -> ID BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> EmpID VARCHAR(200) NOT NULL
    -> );
Query OK, 0 rows affected (0.15 sec)

tidb 5.7.25-TiDB-v5.0.0 > CREATE INDEX idx_EmpID ON Employee(EmpID);
Query OK, 0 rows affected (2.87 sec)

tidb 5.7.25-TiDB-v5.0.0 > EXPLAIN SELECT * FROM Employee WHERE EmpID= 123;
+-------------------------+----------+-----------+----------------+------------------------------------+
| id                      | estRows  | task      | access object  | operator info                      |
+-------------------------+----------+-----------+----------------+------------------------------------+
| TableReader_7           | 8000.00  | root      |                | data:Selection_6                   |
| └─Selection_6           | 8000.00  | cop[tikv] |                | eq(cast(test.employee.empid), 123) |
|   └─TableFullScan_5     | 10000.00 | cop[tikv] | table:Employee | keep order:false, stats:pseudo     |
+-------------------------+----------+-----------+----------------+------------------------------------+
3 rows in set (0.01 sec)

tidb 5.7.25-TiDB-v5.0.0 > EXPLAIN SELECT * FROM Employee WHERE EmpID= '123';
+------------------------+---------+-----------+----------------------------------------+-----------------------------------------------------+
| id                     | estRows | task      | access object                          | operator info                                       |
+------------------------+---------+-----------+----------------------------------------+-----------------------------------------------------+
| IndexReader_6          | 10.00   | root      |                                        | index:IndexRangeScan_5                              |
| └─IndexRangeScan_5     | 10.00   | cop[tikv] | table:Employee, index:idx_EmpID(EmpID) | range:["123","123"], keep order:false, stats:pseudo |
+------------------------+---------+-----------+----------------------------------------+-----------------------------------------------------+
2 rows in set (0.00 sec)

tidb 5.7.25-TiDB-v5.0.0 > INSERT INTO Employee(EmpID) VALUES ('010'),('10');
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

tidb 5.7.25-TiDB-v5.0.0 > SELECT * FROM Employee;
+----+-------+
| ID | EmpID |
+----+-------+
|  1 | 010   |
|  2 | 10    |
+----+-------+
2 rows in set (0.00 sec)

tidb 5.7.25-TiDB-v5.0.0 > SELECT * FROM Employee WHERE EmpID='10';
+----+-------+
| ID | EmpID |
+----+-------+
|  2 | 10    |
+----+-------+
1 row in set (0.00 sec)

tidb 5.7.25-TiDB-v5.0.0 > SELECT * FROM Employee WHERE EmpID=10;
+----+-------+
| ID | EmpID |
+----+-------+
|  1 | 010   |
|  2 | 10    |
+----+-------+
2 rows in set (0.00 sec)

Can you tell me whether my updated c# logic is correct and it would be used for indexing? — suresh rajput, Apr 19 '21 at 05:31
The C# logic looks correct to me. However I'm not a C# expert so maybe someone else can make a better judgement on that. — Daniël van Eeden, Apr 19 '21 at 09:13

TiDB indexes are not getting used for queries

1 Answers1