Issue
I have a simple spring JPA select query which is taking too much to execute .
Table contains ~34 million records.
Query : select * from temp where cust_id=211313131;
taking time >1sec
SLow Query logs : Query_time: 0.990815 Lock_time: 0.000034 Rows_sent: 0 Rows_examined: 3273885 Rows_affected: 0
Table structure :
mysql> desc temp;
+-------------+--------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+-------------------+-----------------------------------------------+
| id | bigint | NO | PRI | NULL | auto_increment |
| cust_id | varchar(100) | YES | MUL | NULL | |
| amount | double(11,3) | YES | | NULL | |
| is_enabled | int | NO | MUL | 1 | |
| created_at | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| updated_at | timestamp | NO | MUL | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+--------------+------+-----+-------------------+-----------------------------------------------+
6 rows in set (0.05 sec)
Index on table :
mysql> show index from temp;
+-------------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| temp | 0 | PRIMARY | 1 | id | A | 285 | NULL | NULL | | BTREE | | | YES | NULL |
| temp | 1 | idx_subscribers_cust_id | 1 | cust_id | A | 281 | NULL | NULL | YES | BTREE | | | YES | NULL |
| temp | 1 | idx_subscribers_is_enabled | 1 | is_enabled | A | 1 | NULL | NULL | | BTREE | | | YES | NULL |
| temp | 1 | idx_subscribers_updated_at | 1 | updated_at | A | 264 | NULL | NULL | | BTREE | | | YES | NULL |
+-------------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.21 sec)
I have checked with explain, as explain result show it should execute immediately.
mysql> explain select * from temp where cust_id="31231234343";
+----+-------------+-------------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | temp | NULL | ref | idx_subscribers_cust_id | idx_subscribers_cust_id | 403 | const | 1 | 100.00 | NULL |
+----+-------------+-------------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
Can any one help me to find out why it is taking >1sec to fetch the record even index is present on that column.
Solution
One suspicion related with implicit type conversion.
If query looks like this:
select * from temp where cust_id = 1,
then mysql try to convert column to fit type of parameter. Internally query is executed like this:
select * from temp where cast(cust_id as UNSIGNED) =1
I'm not sure about exact numeric type MYSQL converts to, but quite sure, that for this condition no existing key will be used
Answered By - Kadet
Answer Checked By - Senaida (JavaFixing Volunteer)
