Mysql randomly refuses to use indexes for even simple queries. 1 example from slow-query log:
# Time: 130904 12:19:59
# User@Host: cron[cron] @ [10.71.41.38]
# Query_time: 58.488760 Lock_time: 0.000029 Rows_sent: 0 Rows_examined: 13634899
SET timestamp=1378311599;
UPDATE dial_list set status='AB' where lead_id = '3335602076';
This is a MYISAM table with lead_id as primary key
| Field | Type | Null | Key | Default |Extra |
| lead_id | int(9) unsigned | NO | PRI | NULL | auto_increment |
The same query run on an identical server with similar data size uses the index. Also, not all such queries on this server turn up as table scans. What could be the problem?
SHOW CREATE TABLE dial_list\G
2) Please tell me, does the table experience heavy INSERTs, UPDATEs, and DELETEs ??? – RolandoMySQLDBA Sep 4 '13 at 19:43WHERE string_column = 100
. The conversion used in this question wouldn't have this issue - even if it's not advised to useWHERE int_column = '100'
. – ypercubeᵀᴹ Mar 27 '18 at 8:51