4

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?

|improve this question
  • 1) Please run 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:43
  • 4
    Is the fact that you're comparing a string and int the issue: stackoverflow.com/questions/16786063/… – dcaswell Sep 4 '13 at 20:55
  • @RolandoMySQLDBA yes, it expereinces heavy Inserts, Updates, Selects but not deletes. Inserts are only in beginning of day, and have not been done just before this query – warmC Sep 5 '13 at 4:02
  • @dcaswell No, the question linked is about the opposite conversion: WHERE string_column = 100. The conversion used in this question wouldn't have this issue - even if it's not advised to use WHERE int_column = '100'. – ypercubeᵀᴹ Mar 27 '18 at 8:51
0

From the comments dcaswell,

Is the fact that you're comparing a string and int the issue

He suggests you check out

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.