why don't work for my substring index?

[zhenxing zhai]

my sql is:
select u.username,u.fullname,w.name,w.description,w.pubtime,sum(r.dayhits) as hit from rolleruser u, website w, referer r where LEFT(r.datestr,6)=DATE_FORMAT(now(),'%Y%m') and r.websiteid=w.id and w.userid=u.id and u.userenabled=1 and w.groupmode=0 group by u.username,u.fullname,w.name,w.description,w.pubtime order by hit desc limit 51;


explain plan as follow:
+----+-------------+-------+--------+--------------------------------------------------------------------------+---------+---------+------------------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+--------------------------------------------------------------------------+---------+---------+------------------+--------+----------------------------------------------+
| 1 | SIMPLE | r | ALL | idx_referer_websiteid | NULL | NULL | NULL | 980128 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | w | eq_ref | PRIMARY,idx_website_userid,idx_website_groupmode | PRIMARY | 4 | riji.r.WEBSITEID | 1 | Using where |
| 1 | SIMPLE | u | eq_ref | PRIMARY,idx_rolleruser_userenabled,idx_rolleruser_userenabled_experience | PRIMARY | 4 | riji.w.USERID | 1 | Using where |
+----+-------------+-------+--------+--------------------------------------------------------------------------+---------+---------+------------------+--------+----------------------------------------------+

query is so slow because scan full table for table r,but having index on r.datestr and r.datestr(6)

mysql> show index from referer;
+---------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| referer | 0 | PRIMARY | 1 | id | A | 980128 | NULL | NULL | | BTREE | |
| referer | 1 | idx_referer_dayhits | 1 | DAYHITS | A | 944 | NULL | NULL | | BTREE | |
| referer | 1 | idx_referer_totalhits | 1 | TOTALHITS | A | 950 | NULL | NULL | | BTREE | |
| referer | 1 | idx_referer_reftime | 1 | REFTIME | A | 326709 | NULL | NULL | | BTREE | |
| referer | 1 | idx_referer_websiteid | 1 | WEBSITEID | A | 163354 | NULL | NULL | YES | BTREE | |
| referer | 1 | idx_referer_datestr | 1 | datestr | A | 300 | NULL | NULL | YES | BTREE | |
| referer | 1 | idx_referer_entryid | 1 | ENTRYID | A | 688 | NULL | NULL | YES | BTREE | |
| referer | 1 | idx_referer_datestr_sub6 | 1 | datestr | A | 23 | 6 | NULL | YES | BTREE | |
+---------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
8 rows in set (0.00 sec)

if I change condition 'LEFT(r.datestr,6)=DATE_FORMAT(now(),'%Y%m') ' to 'r.datestr=DATE_FORMAT(now(),'%Y%m%d') ' ,explain plan as follow:

mysql> desc select u.username,u.fullname,w.name,w.description,w.pubtime,sum(r.dayhits) as hit from rolleruser u, website w, referer r where r.datestr=DATE_FORMAT(now(),'%Y%m%d') and r.websiteid=w.id and w.userid=u.id and u.userenabled=1 and w.groupmode=0 group by u.username,u.fullname,w.name,w.description,w.pubtime order by hit desc limit 51;
+----+-------------+-------+--------+--------------------------------------------------------------------------+---------------------+---------+------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+--------------------------------------------------------------------------+---------------------+---------+------------------+------+----------------------------------------------+
| 1 | SIMPLE | r | ref | idx_referer_websiteid,idx_referer_datestr,idx_referer_datestr_sub6 | idx_referer_datestr | 21 | const | 1 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | w | eq_ref | PRIMARY,idx_website_userid,idx_website_groupmode | PRIMARY | 4 | riji.r.WEBSITEID | 1 | Using where |
| 1 | SIMPLE | u | eq_ref | PRIMARY,idx_rolleruser_userenabled,idx_rolleruser_userenabled_experience | PRIMARY | 4 | riji.w.USERID | 1 | Using where |
+----+-------------+-------+--------+--------------------------------------------------------------------------+---------------------+---------+------------------+------+----------------------------------------------+
3 rows in set (0.05 sec)

query so fast but result not what I want? why occur this?why substring index don't work?anyone can help me? thanks!!