MySQL Forums
Forum List  »  Optimizer & Parser

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

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!!

Options: ReplyQuote


Subject
Views
Written By
Posted
why don't work for my substring index?
4133
November 06, 2005 10:09PM


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.