Johan Johan - 9 days ago 6
C# Question

Index is not used when using a variable

Background: Server application (C#) fetches order data from the database using the query below (simplified). I log the average time this takes and now there is a sudden increase in execution time (from 5ms to 50ms on average). So I went to check this query as a start.

The query is executed from the server app as following:

String ordernr = "123456789";
String sql = "SELECT * FROM MYDB.`MYTABLE` WHERE id = @id";
using (MySqlCommand cmd = new MySqlCommand(sql, conn))
{
cmd.Parameters.Add("@id", MySqlDbType.VarChar, 16).Value = ordernr;
using (MySqlDataReader reader = cmd.ExecuteReader())
{
//reading data
}
}


When I check the query in mysql , I get the following results:

MariaDB [MYDB]> SET @id = '123456789';
Query OK, 0 rows affected (0.00 sec)

MariaDB [MYDB]> explain SELECT SQL_NO_CACHE * FROM MYDB.`MYTABLE` WHERE id = @id ;
+------+-------------+-----------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | MYTABLE | ALL | NULL | NULL | NULL | NULL | 1448219 | Using where |
+------+-------------+-----------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

MariaDB [MYDB]> explain SELECT SQL_NO_CACHE * FROM MYDB.`MYTABLE` WHERE id = '123456789';
+------+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | MYTABLE | const | PRIMARY | PRIMARY | 18 | const | 1 | |
+------+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

MariaDB [MYDB]>


Now my question is, why is the PRIMARY index ignored when I use a variable?

I don't know if this is the reason for the drop in performance (as both queries return in 0.00 sec. in this example), but it made me frown why there is a difference. I use a prepared query with a variable in the server app too. So I want to check if this is related.

Could anyone explain this?

Answer

The index length indicates that id is actually a char/varchar column (VARCHAR(16) would be my guess).

Then, the problem is that the character set of the table or column and one of the connection don't match. Consider the following:

MariaDB [test]> create table t1 (id varchar(16) primary key, i int) charset latin1;
Query OK, 0 rows affected (0.72 sec)

MariaDB [test]> insert into t1 values ('123456789',1),('987654321',2);
Query OK, 2 rows affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [test]> set names latin1;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> set @a='123456789';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> explain select * from t1 where id = @a;
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id   | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|    1 | SIMPLE      | t1    | const | PRIMARY       | PRIMARY | 18      | const |    1 |       |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

MariaDB [test]> set names utf8;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> set @a='123456789';
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> explain select * from t1 where id = @a;
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

You need to either alter the table, or to set the character set in the session, or use explicit conversion:

MariaDB [test]> explain select * from t1 where id = @a;
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

MariaDB [test]> explain select * from t1 where id = convert(@a using latin1);
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id   | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|    1 | SIMPLE      | t1    | const | PRIMARY       | PRIMARY | 18      | const |    1 |       |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+