I have a table, here is the structure:

CREATE TABLE `table_data_cache` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `date` date DEFAULT NULL,
  `enterTollgateNumber` bigint(20) DEFAULT NULL,
  `exitTollgateNumber` bigint(20) DEFAULT NULL,
  `carType` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `distanceBetweenTollgate` decimal(19,2) DEFAULT NULL,
  `hour0` bigint(20) DEFAULT '0',
  `hour1` bigint(20) DEFAULT '0',
  `hour2` bigint(20) DEFAULT '0',
  `hour3` bigint(20) DEFAULT '0',
  `hour4` bigint(20) DEFAULT '0',
  `hour5` bigint(20) DEFAULT '0',
  `hour6` bigint(20) DEFAULT '0',
  `hour7` bigint(20) DEFAULT '0',
  `hour8` bigint(20) DEFAULT '0',
  `hour9` bigint(20) DEFAULT '0',
  `hour10` bigint(20) DEFAULT '0',
  `hour11` bigint(20) DEFAULT '0',
  `hour12` bigint(20) DEFAULT '0',
  `hour13` bigint(20) DEFAULT '0',
  `hour14` bigint(20) DEFAULT '0',
  `hour15` bigint(20) DEFAULT '0',
  `hour16` bigint(20) DEFAULT '0',
  `hour17` bigint(20) DEFAULT '0',
  `hour18` bigint(20) DEFAULT '0',
  `hour19` bigint(20) DEFAULT '0',
  `hour20` bigint(20) DEFAULT '0',
  `hour21` bigint(20) DEFAULT '0',
  `hour22` bigint(20) DEFAULT '0',
  `hour23` bigint(20) DEFAULT '0',
  `countHourer` bigint(20) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `DateIndex` (`date`) USING HASH,
  KEY `QrySearchStandard` (`date`,`enterTollgateNumber`,`exitTollgateNumber`,`carType`),
  KEY `DateIndexBetween` (`date`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=7797493 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

2nd table:

CREATE TABLE `total_each_day` (
  `date` date NOT NULL,
  `normalCarTotal` double DEFAULT NULL,
  `bigCarTotal` double DEFAULT NULL,
  `counter` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`date`),
  KEY `Index 2` (`date`) USING HASH
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

3rd table:

CREATE TABLE `ramp_block` (
  `id` bigint(20) NOT NULL,
  `classification` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `placeName` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `rampBlockName` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `tollgateCode` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `tollgateName` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `tollgateName2` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

In 1st table I have created 3 indexes but they seem not to be used. Here is my query:

select 
    (c.normalCar + c.bigCar) as correctionNow,
    (t.normalCarTotal + t.bigCarTotal) as totalNow,
    (c2.normalCar + c2.bigCar) as correctionYesterday,
    (t2.normalCarTotal + t2.bigCarTotal) as totalYesterday,     
    DATE(td.DATE) AS realDate,
 from table_data_cache  as td
    left outer join total_each_day as t on date(t.date) = date(td.date)
    left outer join total_each_day as t2 on date(t2.date) = date_sub(date(td.date) , interval 1 day)
    left outer join correction as c on date(c.date) = date(td.date)
    left outer join correction as c2 on date(c2.date) = date_sub(date(td.date) , interval 1 day)

     where  td.enterTollgateNumber in (3,4,7,13,19,23)
       and  td.exitTollgateNumber in (2,5,9,15,17,21,25,29,33,37,40,41) 
       and date(td.date) >= date('2009-01-01')
       and date(td.date) <= date('2009-01-02') 
       and carType = 1  

but index seems not appear

+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | td    | ALL  | NULL          | NULL | NULL    | NULL | 6570156 | Using where |
|  1 | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL |     357 |             |
|  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |     357 |             |
|  1 | SIMPLE      | c     | ALL  | NULL          | NULL | NULL    | NULL |      30 |             |
|  1 | SIMPLE      | c2    | ALL  | NULL          | NULL | NULL    | NULL |      30 |             |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+

What's wrong with my index here? I can't resolve it until now.

  • 2
    I'm not sure about MySQL, but in for instance SQL Server when you apply a function to a column in the WHERE clause (such as your date() function) it negates the use of indexes because it forces a table scan. – JNK Sep 25 '14 at 12:29
  • @JNK is 100% right. The date(t.date) = date(td.date) is redundant and it will only slow down the query. Use t.date = td.date instead. Since both columns are DATE datatype, the query will be equivalent. – ypercubeᵀᴹ Sep 25 '14 at 12:33
  • yeah that works – hilmanshini Sep 25 '14 at 12:38
  • Change the where as well. The and date(td.date) >= date('2009-01-01') and date(td.date) <= date('2009-01-02') could be written as: and td.date >= date('2009-01-01') and td.date <= date('2009-01-02') – ypercubeᵀᴹ Sep 25 '14 at 12:50

after @JNK and @ypercube giving me an advice

i found the solution

the problem is in date() function take a look useless date function in my query

select 
    (c.normalCar + c.bigCar) as correctionNow,
    (t.normalCarTotal + t.bigCarTotal) as totalNow,
    (c2.normalCar + c2.bigCar) as correctionYesterday,
    (t2.normalCarTotal + t2.bigCarTotal) as totalYesterday,     
    DATE(td.DATE) AS realDate,
 from table_data_cache  as td
    left outer join total_each_day as t on date(t.date) = date(td.date)
    left outer join total_each_day as t2 on date(t2.date) = date_sub(date(td.date) , interval 1 day)
    left outer join correction as c on date(c.date) = date(td.date)
    left outer join correction as c2 on date(c2.date) = date_sub(date(td.date) , interval 1 day)

     where  td.enterTollgateNumber in (3,4,7,13,19,23)
       and  td.exitTollgateNumber in (2,5,9,15,17,21,25,29,33,37,40,41) 
       and date(td.date) >= date('2009-01-01')
       and date(td.date) <= date('2009-01-02') 
       and carType = 1  

and i will remove all date() function

so the final works query should be like this

select 
    (c.normalCar + c.bigCar) as correctionNow,
    (t.normalCarTotal + t.bigCarTotal) as totalNow,
    (c2.normalCar + c2.bigCar) as correctionYesterday,
    (t2.normalCarTotal + t2.bigCarTotal) as totalYesterday,     
    td.DATE AS realDate,
 from table_data_cache  as td



    left outer join total_each_day as t on t.date = td.date
    left outer join total_each_day as t2 on t2.date = date_sub(td.date , interval 1 day)
    left outer join correction as c on c.date = td.date
    left outer join correction as c2 on c2.date = date_sub(td.date , interval 1 day)
     where  td.enterTollgateNumber in (3,4,7,13,19,23) 
       and  td.exitTollgateNumber in (2,5,9,15,17,21,25,29,33,37,40,41)   
       and td.date >= date('2009-01-01') 
       and td.date <= date('2009-01-02') 
       and carType = 1  

here are the result

+----+-------------+-------+--------+----------------------------------------------+-----------+---------+-----------------+------+-------------+
| id | select_type | table | type   | possible_keys                                | key       | key_len | ref             | rows | Extra       |
+----+-------------+-------+--------+----------------------------------------------+-----------+---------+-----------------+------+-------------+
|  1 | SIMPLE      | td    | range  | DateIndex,QrySearchStandard,DateIndexBetween | DateIndex | 4       | NULL            |    1 | Using where |
|  1 | SIMPLE      | t     | eq_ref | PRIMARY                                      | PRIMARY   | 3       | traffic.td.date |    1 |             |
|  1 | SIMPLE      | t2    | eq_ref | PRIMARY                                      | PRIMARY   | 3       | func            |    1 |             |
|  1 | SIMPLE      | c     | eq_ref | PRIMARY                                      | PRIMARY   | 8       | traffic.td.date |    1 |             |
|  1 | SIMPLE      | c2    | eq_ref | PRIMARY                                      | PRIMARY   | 8       | func            |    1 |             |
+----+-------------+-------+--------+----------------------------------------------+-----------+---------+-----------------+------+-------------+

thanks again @ypercube and @JNK

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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