0

This is my table schema along with defined indexes:

CREATE TABLE `chat_state_event` (
  `id` bigint(20) NOT NULL,
  `agentId` varchar(255) DEFAULT NULL,
  `allocState` varchar(255) NOT NULL,
  `chatId` varchar(255) DEFAULT NULL,
  `chatState` varchar(255) NOT NULL,
  `conversationId` varchar(255) NOT NULL,
  `event_time` datetime(3) DEFAULT NULL,
  `reason` varchar(255) DEFAULT NULL,
  `addedOn` datetime(3) DEFAULT NULL,
  `modifiedOn` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `conversationIdIndex` (`conversationId`),
  KEY `conversationId_eventTs` (`conversationId`,`event_time`),
  KEY `eventTs_reason` (`event_time`,`reason`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Buffer Pool Size : 3 GB

Table Rows : Around 2 million

  • Slow Query (Time taken : 11 sec):
SELECT *
from chat_state_event A,
     (select agentId as agentLdap, max(event_time) as max_event_time
      from chat_state_event
      where event_time between ('2019-04-29 19:08:20.03') 
                       and ('2019-04-30 19:08:20.03')
        and agentId in
            ('techmn.1617832', 'techmn.1609693', 'techmn.1614422', 
              'techmn.1682295', 'techmn.1682291', 'techmn.1682901',
             'techmn.600256', 'techmn.1614478', 'techmn.1619099')
      group by agentId
      order by event_time desc) B
where A.event_time = B.max_event_time
  and A.agentId = B.agentLdap;

Its explain output:

id  select_type table   type    possible_keys   key key_len ref rows    Extra

1   PRIMARY <derived2>  ALL NULL    NULL    NULL    NULL    362664  Using where
1   PRIMARY A   ref eventTs_reason  eventTs_reason  8   B.max_event_time    1   Using where
2   DERIVED chat_state_event    ALL eventTs_reason  NULL    NULL    NULL    1876419 **Using where; Using temporary; Using filesort**
  • Query Running fast (Time taken :2 sec):
SELECT *
from chat_state_event A,
     (select agentId as agentLdap, max(event_time) as max_event_time
      from chat_state_event **FORCE INDEX (eventTs_reason)**
      where event_time between ('2019-04-29 19:08:20.03') 
                           and ('2019-04-30 19:08:20.03') 
      and agentId in ('techmn.1617832'
          , 'techmn.1609693'
          , 'techmn.1614422'
          , 'techmn.1682295'
          , 'techmn.1682291'
          , 'techmn.1682901'
          , 'techmn.600256'
          , 'techmn.1614478'
          , 'techmn.1619099')
      group by agentId
      order by event_time desc) B
where A.event_time = B.max_event_time
  and A.agentId = B.agentLdap;

Its explain output :

1   PRIMARY <derived2>  ALL NULL    NULL    NULL    NULL    362664  Using where
1   PRIMARY A   ref eventTs_reason  eventTs_reason  8   B.max_event_time    1   Using where
2   DERIVED chat_state_event    range   eventTs_reason  eventTs_reason  8   NULL    362664  **Using index condition; Using where; Using temporary; Using filesort**

I am unable to understand why MySQL is not using the index in first query while using the index in the second query. The time in which output is given has substantial difference.

  • Try adding an index on (agent_id, event_time), remove the FORCE. Oh, and the ORDER BY, which is useless anyway. – ypercubeᵀᴹ May 1 at 17:25
  • @ypercubeᵀᴹ, thanks for your suggestion. I am trying to understand why the already provided index is not working. When I am trying to run the same query on different server (i.e our pre-prod , it has less data as compared to production), it using index condition there. – Shitiz Bhutani Software Develo May 2 at 6:41

Your Answer

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

Browse other questions tagged or ask your own question.