Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

mysql> show create table dial_list\G
************* 1. row *************
Table: dial_list
Create Table: CREATE TABLE dial_list (
lead_id int(9) unsigned NOT NULL AUTO_INCREMENT,
entry_date datetime DEFAULT NULL,
modify_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
status varchar(6) DEFAULT NULL,
user varchar(20) DEFAULT NULL,
vendor_lead_code varchar(20) DEFAULT NULL,
source_id varchar(50) DEFAULT NULL,
list_id bigint(14) unsigned NOT NULL DEFAULT '0',
gmt_offset_now decimal(4,2) DEFAULT '0.00',
called_since_last_reset enum('Y','N','Y1','Y2','Y3','Y4','Y5','Y6','Y7','Y8','Y9','Y10') DEFAULT 'N',
phone_code varchar(10) DEFAULT NULL,
phone_number varchar(18) NOT NULL,
title varchar(4) DEFAULT NULL,
first_name varchar(30) DEFAULT NULL,
middle_initial varchar(1) DEFAULT NULL,
last_name varchar(30) DEFAULT NULL,
address1 varchar(100) DEFAULT NULL,
address2 varchar(100) DEFAULT NULL,
address3 varchar(100) DEFAULT NULL,
city varchar(50) DEFAULT NULL,
state varchar(2) DEFAULT NULL,
province varchar(50) DEFAULT NULL,
postal_code varchar(10) DEFAULT NULL,
country_code varchar(3) DEFAULT NULL,
gender enum('M','F','U') DEFAULT 'U',
date_of_birth date DEFAULT NULL,
alt_phone varchar(12) DEFAULT NULL,
email varchar(70) DEFAULT NULL,
security_phrase varchar(100) DEFAULT NULL,
comments varchar(255) DEFAULT NULL,
called_count smallint(5) unsigned DEFAULT '0',
last_local_call_time datetime DEFAULT NULL,
rank smallint(5) NOT NULL DEFAULT '0',
owner varchar(20) DEFAULT '',
entry_list_id bigint(14) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (lead_id),
KEY phone_number (phone_number),
KEY list_id (list_id),
KEY called_since_last_reset (called_since_last_reset),
KEY status (status),
KEY gmt_offset_now (gmt_offset_now),
KEY postal_code (postal_code), KEY last_local_call_time (last_local_call_time),
KEY rank (rank),
KEY owner (owner),
KEY phone_list (phone_number,list_id),
KEY list_phone (list_id,phone_number),
KEY list_status (list_id,status)
) ENGINE=MyISAM AUTO_INCREMENT=35830810 DEFAULT CHARSET=latin1

share|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
2  
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.