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.

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: Screenshot for explain

share|improve this question
    
You should generally add EXPLAIN of your query for such questions. But in this case I think I see a possible problem - will answer. –  jkavalik Jun 11 at 7:46
    
Thanks, I have added explain result in my question. –  Ashy saini Jun 11 at 7:58

2 Answers 2

up vote 0 down vote accepted

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 <,>...) 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.

share|improve this answer
    
Seeing your added explain, it seems to actually use index condition, so thats good, but I see many of your columns are varchar. It is often better to use int/bigint IDs where possible, because varchar is much bigger and variable length messes estimates a bit. –  jkavalik Jun 11 at 8:02

Consider using ENUM for status, event, type. ENUM is 1 byte, not many.

The optimizer is using only company_id from the index. Consider adding:

INDEX(company_id, start_time)

ICP helps a little, but having fields in the index fields that can be directly used helps a lot more.

share|improve this answer

Your Answer

 
discard

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

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