-1

select *from REPT_AIR_PRY_HY1 RAP where (RAP.DATE_OF_ISSUE) BETWEEN "2017-10-01" AND DATE_ADD("2017-10-31", INTERVAL 1 DAY)

the explain plan of this query gives me 337243 but data s between these dates is only 55209 and there is even index is created on column DATE_OF_ISSUE . So, why it is scanning the whole table? thanks in advance

sathish Anandan
  • 1
  • 8

1 Answers1

0

Some possibilities:

  • The optimizer thinks (correctly or incorrectly) that a non-trivial percentage of the table would be needed. How many rows in the table?
  • You are using MyISAM; switch to InnoDB.
  • For some reason, the 'statistics' are stale. Do ANALYZE TABLE.

To discuss further, please provide SHOW CREATE TABLE.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • 1. the total records found in the specified table is 408025, but the carnality of index created for DATE_OF_ISSUE column is 337243 . 2.am using InnoDB only. – sathish Anandan Mar 07 '18 at 10:39
  • All statistics, including that cardinality, are approximations. That's off by 20% -- I've seen it off by a factor of 2 sometimes. Newer versions of MySQL tend to be slightly better. – Rick James Mar 07 '18 at 15:39
  • So there is no solution for this. – sathish Anandan Mar 08 '18 at 06:21