MySQL Forums
Forum List  »  Optimizer & Parser

Re: why don't work for my substring index?
Posted by: zhenxing zhai
Date: November 13, 2005 09:23PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
4137
November 06, 2005 10:09PM
Re: why don't work for my substring index?
2472
November 13, 2005 09:23PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.