explain on Roller table
mysql> source plan.sql
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row in set (0.00 sec)
EXPLAIN PLAN SET STATEMENT_ID = 'roll_id' FOR SELECT t0.id, t0.content, t0.contenttype, t0.email, t0.name, t0.notify, t0.plugins, t0.posttime, t0.referrer, t0.remotehost, t0.status, t0.url, t0.useragent, t1.id, t1.allowcomments, t1.anchor, t2.id, t2.description, t2.image, t2.name, t3.id, t3.description, t3.image, t3.name, t3.path,t3.websiteid, t2.path, t4.id, t4.about, t4.isactive, t4.allowcomments, t4.blacklist, t5.id, t5.description, t5.image, t5.name, t5.path,t5.websiteid, t6.id, t6.activationcode, t6.datecreated, t6.emailaddress,
t6.isenabled, t6.fullname, t6.locale, t6.passphrase, t6.screenname,t6.timeZone, t6.username, t4.customstylesheet, t4.datecreated, t4.defaultallowcomments, t7.id, t7.description, t7.image, t7.name,t7.path, t7.websiteid, t4.defaultcommentdays, t4.defaultpageid,t4.defaultplugins, t4.description, t4.editorpage, t4.editortheme,
t4.emailaddress, t4.emailcomments, t4.emailfromaddress,t4.enablebloggerapi, t4.enablemultilang, t4.isenabled, t4.displaycnt,t4.handle, t4.icon, t4.lastmodified, t4.locale, t4.commentmod,t4.name, t4.pagemodels, t4.showalllangs, t4.timeZone, t4.weblogdayid, t1.commentdays, t1.content_src, t1.content_type, t8.id, t8.activationcode,
t8.datecreated, t8.emailaddress, t8.isenabled, t8.fullname, t8.locale, t8.passphrase, t8.screenname, t8.timeZone, t8.username, t1.link, t1.locale,t1.pinnedtomain, t1.plugins, t1.pubtime, t1.righttoleft, t1.status, t1.summary,
t1.text, t1.title, t1.updatetime, t9.id, t9.about, t9.isactive, t9.allowcomments, t9.blacklist, t9.bloggercatid, t9.userid, t9.customstylesheet, t9.datecreated, t9.defaultallowcomments, t9.defaultcatid, t9.defaultcommentdays, t9.defaultpageid,t9.defaultplugins, t9.description, t9.editorpage, t9.editortheme, t9.emailaddress,
t9.emailcomments, t9.emailfromaddress, t9.enablebloggerapi, t9.enablemultilang,
t9.isenabled, t9.displaycnt, t9.handle, t9.icon, t9.lastmodified, t9.locale, t9.commentmod, t9.name, t9.pagemodels, t9.showalllangs, t9.timeZone, t9.weblogdayid FROM roller_comment t0 INNER JOIN weblogentry t1 ON t0.entryid = t1.id LEFT OUTER JOIN weblogcategory t2 ON t1.categoryid = t2.id
LEFT OUTER JOIN rolleruser t8 ON t1.userid = t8.id
LEFT OUTER JOIN website t9 ON t1.websiteid = t9.id
LEFT OUTER JOIN weblogcategory t3 ON t2.parentid = t3.id
LEFT OUTER JOIN website t4 ON t2.websiteid = t4.id
LEFT OUTER JOIN weblogcategory t5 ON t4.bloggercatid = t5.id
LEFT OUTER JOIN rolleruser t6 ON t4.userid = t6.id
LEFT OUTER JOIN weblogcategory t7 ON t4.defaultcatid = t7.id
WHERE (t1.websiteid = '8a926693072c38bf010741c83fcb36d6' AND t0.status = 'APPROVED')
ORDER BY t0.posttime DESC --LIMIT 0, 10
/
Rows Plan
---------- -------------------------------------------------------------------
1 SELECT STATEMENT
1 NESTED LOOPS OUTER
1 NESTED LOOPS OUTER
1 NESTED LOOPS OUTER
1 NESTED LOOPS OUTER
1 NESTED LOOPS OUTER
1 NESTED LOOPS OUTER
1 NESTED LOOPS OUTER
1 NESTED LOOPS OUTER
1 NESTED LOOPS
1 TABLE ACCESS BY INDEX ROWID ROLLER_COMMENT
Rows Plan
---------- -------------------------------------------------------------------
1 INDEX RANGE SCAN DESCENDING CO_COMBO1_IDX
1 TABLE ACCESS BY INDEX ROWID WEBLOGENTRY
1 INDEX UNIQUE SCAN SYS_C00136183
1 TABLE ACCESS BY INDEX ROWID WEBSITE
1 INDEX UNIQUE SCAN SYS_C00136144
1 TABLE ACCESS BY INDEX ROWID ROLLERUSER
1 INDEX UNIQUE SCAN SYS_C00136094
1 TABLE ACCESS BY INDEX ROWID WEBLOGCATEGORY
1 INDEX UNIQUE SCAN SYS_C00136164
1 TABLE ACCESS BY INDEX ROWID WEBSITE
1 INDEX UNIQUE SCAN SYS_C00136144
Rows Plan
---------- -------------------------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID WEBLOGCATEGORY
1 INDEX UNIQUE SCAN SYS_C00136164
1 TABLE ACCESS BY INDEX ROWID ROLLERUSER
1 INDEX UNIQUE SCAN SYS_C00136094
1 TABLE ACCESS BY INDEX ROWID WEBLOGCATEGORY
1 INDEX UNIQUE SCAN SYS_C00136164
1 TABLE ACCESS BY INDEX ROWID WEBLOGCATEGORY
1 INDEX UNIQUE SCAN SYS_C00136164
Posted at 03:07PM mar 10, 2008 by Boris in Roller |