full table scan in mysql

Question

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

Answer 1

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.