Mysql query is using some other index We have 2 tables:

 CREATE TABLE `allcallers` (
  `id` varchar(50) NOT NULL DEFAULT '',
  `company_id` varchar(50) NOT NULL,
  `clid` varchar(30) NOT NULL,
  `clid_raw` varchar(50) NOT NULL,
  `uid` varchar(32) NOT NULL,
  `channel_id` varchar(100) NOT NULL,
  `start_time` datetime NOT NULL,
  `end_time` datetime NOT NULL,
  `type` varchar(30) NOT NULL COMMENT 'Call,SMS,Fax',
  `event` varchar(30) NOT NULL COMMENT 'Incoming/Outgoing',
  `status` varchar(20) NOT NULL COMMENT 'failed/success/bridege/missed',
  `via` varchar(50) NOT NULL COMMENT 'department',
  `department_id` varchar(50) NOT NULL,
  `filename` varchar(50) NOT NULL,
  `file_url` varchar(200) NOT NULL,
  `state` int(5) DEFAULT NULL,
  `country` int(5) NOT NULL,
  `rdnis` varchar(50) NOT NULL,
  `is_billable` tinyint(4) NOT NULL DEFAULT '0',
  `is_synced` int(2) NOT NULL,
  `star_type` int(2) DEFAULT '0',
  `is_archived` int(2) DEFAULT '0' COMMENT '0=not archived ,So show in logs\n1=archived ,Don''t show ',
  `server` varchar(20) NOT NULL,
  `notification_status` tinyint(4) NOT NULL DEFAULT '0',
  `subscription_status` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `start_time` (`start_time`) USING BTREE,
  KEY `uid` (`uid`) USING BTREE,
  KEY `Index 4` (`company_id`,`clid`,`via`,`status`) USING BTREE,
  KEY `is_synced` (`is_synced`),
  KEY `remarketing1` (`subscription_status`,`start_time`) USING BTREE,
  KEY `remarketing2` (`subscription_status`,`department_id`) USING BTREE,
  KEY `remarketing3` (`company_id`,`type`,`status`,`event`) USING BTREE,
  KEY `allcaller_allcaller_logs` (`start_time`,`type`,`event`,`is_billable`,`end_time`,`company_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 


and second table

CREATE TABLE `allcaller_logs` (
  `id` varchar(50) NOT NULL DEFAULT '',
  `allcaller_id` varchar(50) NOT NULL,
  `property_id` varchar(50) NOT NULL,
  `start_time` datetime NOT NULL,
  `end_time` datetime NOT NULL,
  `uid` varchar(32) NOT NULL,
  `via` varchar(200) NOT NULL,
  `user_id` varchar(50) NOT NULL,
  `transfer_to` varchar(50) DEFAULT NULL,
  `file` varchar(50) NOT NULL,
  `status` tinyint(4) NOT NULL,
  `action` varchar(50) NOT NULL,
  `country_code` varchar(15) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `allcaller_id` (`allcaller_id`,`user_id`,`start_time`) USING BTREE,
  CONSTRAINT `FK_allcaller_logs_allcallers` FOREIGN KEY (`allcaller_id`) REFERENCES `allcallers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |


We are using this query:

SELECT 
  SUM(CEIL(TIME_TO_SEC(TIMEDIFF(C.`end_time`,C.`start_time`))/60)) AS `call_duration`
FROM `allcallers` A 
INNER JOIN allcaller_logs C ON C.allcaller_id = A.id
WHERE A.start_time BETWEEN '2015-05-22 23:59:59' AND '2015-06-10 18:49:29'
  AND A.type = 'call' 
  AND A.event = 'incoming' 
  AND A.is_billable = '1' 
  AND (C.`action` = 'received' OR C.`action` = 'transferred') 
  AND A.end_time != '0000-00-00 00:00:00'  
  AND  A.company_id = '54bcf339a0eeb309';


It should use allcaller_allcaller_logs index but it is using index 4 (which is not even matching the order of conditions in where clause ). can somebody tell me why it is happening. 

Explain result for this query:
allcaller_allcaller_logs starts with start_time but in your query start_time is checked by BETWEEN - thats range scan, not direct ref, so the query cannot simply use remaining columns (I suppose you use MySQL 5.5, in 5.6 there is Index Condition Pushdown Optimization which would be able to utilize that index better). Your index seems to "cover" your query on that table, so optimizer decision is still a bit weird, but there may be other reasons (bad cardinality/statistics) which together override it.

Generally if you make special index for your query, you need to organize all direct ref ("=") columns first, then either those with range (BETWEEN or &lt;,>...) or those used in order by if you have it.

As you have A.end_time != ... - such condition cannot be generally satisfied by index, so even if you need covering index, move such columns to the end of your index.

"which is not even matching the order of conditions in where clause" - this is misunderstanding of column order - WHERE clause evaluates all conditions as boolean expression and is free to reorder then any way it sees will be best, so order in columns in WHERE and order in index do not have to correspond in any way. For index it is better to order them by cardinality and actual usage.