MySQL using index condition

Question

I have two tables with structure that can be described as follows:

CREATE TABLE `sub_schedule` (
  `ScheduleID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `ServiceID` bigint(20) unsigned NOT NULL,
  `RunTime` time NOT NULL,
  `Status` char(1) NOT NULL DEFAULT 'A',
  `Telco` text,
  PRIMARY KEY (`ScheduleID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

CREATE TABLE `mt` (
  `MtID` int(15) unsigned NOT NULL AUTO_INCREMENT,
  `ServiceID` bigint(20) unsigned NOT NULL,
  `Moperator` varchar(10) NOT NULL,
  `Cmd` varchar(20) NOT NULL,
  `CreateDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`MtID`),
  KEY `CreateDate` (`CreateDate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Mt table is big one and the sub_schedule table is holding less then 500 records.

When i try to run this query:

EXPLAIN 
 SELECT m.serviceid
      , m.createdate
      , m.moperator
   FROM mt m
   JOIN sub_schedule ss 
     ON m.serviceid = ss.serviceid 
    AND ss.status = "A" 
    AND ss.telco LIKE CONCAT('%', m.moperator, '%')
  WHERE m.createdate >= addtime((subdate(curdate(), 1)),ss.runtime) 
    AND m.createdate <= addtime((subdate(curdate(), 0)),ss.runtime)
    AND m.cmd LIKE "SUBS%";

It produce this output:

id, select_type, table, type, possible_keys, key  , key_len, ref  , rows     , Extra
 1, SIMPLE     , ss   , ALL , NULL         , NULL , NULL   , NULL ,      470 , Using where
 1, SIMPLE     , m    , ALL , CreateDate   , NULL , NULL   , NULL , 57610462 , Range checked for each record (index map: 0x10)

It seems that it doesn't use index for createdate in that query which result in very long query execution time. Already tried with FORCE INDEX and different approaches to that query and tried to move createdate to the ON condition part and use FORCE INDEX FOR JOIN. So my question is: Is there any way to make mysql actually use index for createdate field?

Can you post which are the indexes you created.? – 웃웃웃웃웃 Dec 23 '14 at 11:13 — 웃웃웃웃웃, Dec 23 '14 at 11:13

score 0 · Answer 1 · edited May 23 '17 at 12:12

0

There is no index on mt.serviceid therefore all rows from mt must be scanned to fulfill the condition m.serviceid = ss.serviceid. Other conditions on this table can and will be checked during the full table scan.

Add an index on mt(serviceid, createdate) or mt(createdate, serviceid) (not sure which one will yield best results).

Note: if you decide to go with and index on mt(createdate, serviceid), then the index on mt(createdate) becomes superfluous.

edited May 23 '17 at 12:12

Community

1
1

answered Dec 23 '14 at 12:13

RandomSeed

29,301
6
52
87

thanks for the reply. i added the `serviceid` index to mt table but it stills scan every record – Tezd Dec 23 '14 at 13:59
How many rows does the query return? How many without the `WHERE` clause? When the query returns most rows, the optimizer may decide not to use an index. Also, please make sure you created a **composite index** on `serviceid` **and** `createdate` (not just `serviceid`). – RandomSeed Dec 23 '14 at 15:06
Nah, even if the index is on `serviceid `only, this index could be used on the JOIN condition. I suppose your query just returns most rows from the table. – RandomSeed Dec 23 '14 at 15:11
I need to double check it. Could provide more clear result tomorrow. Thanks for the help. – Tezd Dec 23 '14 at 15:54

MySQL using index condition

1 Answers1