Re: why don't work for my substring index?

[zhenxing zhai]

explain plan is better than before ,but this sql is still very slow(422 second),anyone can help me?

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 datestr like concat(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;
+----+-------------+-------+--------+--------------------------------------------------+---------------------+---------+------------------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+--------------------------------------------------+---------------------+---------+------------------+--------+----------------------------------------------+
| 1 | SIMPLE | r | range | idx_referer_websiteid,idx_referer_datestr | idx_referer_datestr | 21 | NULL | 142038 | 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 | PRIMARY | 4 | riji.w.USERID | 1 | Using where |
+----+-------------+-------+--------+--------------------------------------------------+---------------------+---------+------------------+--------+----------------------------------------------+
3 rows in set (0.00 sec)

slow queries log show follow:
# Query_time: 422 Lock_time: 0 Rows_sent: 51 Rows_examined: 364807
SET timestamp=1131839472;
select u.username,u.fullname,w.name,w.description,w.pubtime,sum(r.dayhits) as hit from rolleruser u, website w, referer r where date
str like concat(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