By webchick on

Documentation on reading EXPLAIN statements.

Running MySQL 4.0.25

EXPLAIN SELECT DISTINCT i.sid, i.type FROM search_index i INNER JOIN node n ON n.nid = i.sid INNER JOIN users u ON n.uid = u.uid WHERE n.status = 0 AND (i.word LIKE 'search')

table type possible_keys key key_len ref rows Extra
i range sid,word word 50 4314 Using where; Using temporary
n eq_ref PRIMARY,uid,node_status_type PRIMARY 4 i.sid 1 Using where; Distinct
u eq_ref PRIMARY PRIMARY 4 n.uid 1 Using where; Using index; Distinct

EXPLAIN SELECT i.type, i.sid, SUM(i.score/t.count) AS score FROM search_index i INNER JOIN node n ON n.nid = i.sid INNER JOIN users u ON n.uid = u.uid INNER JOIN search_total t ON i.word = t.word WHERE n.status = 0 AND (i.word LIKE 'search') GROUP BY i.type, i.sid ORDER BY score DESC LIMIT 0, 10

table type possible_keys key key_len ref rows Extra
i range sid,word word 50 4314 Using where; Using temporary; Using filesort
n eq_ref PRIMARY,uid,node_status_type PRIMARY 4 i.sid 1 Using where
u eq_ref PRIMARY PRIMARY 4 n.uid 1 Using where; Using index
t eq_ref PRIMARY PRIMARY 50 i.word 1

EXPLAIN SELECT DISTINCT i.sid, i.type FROM search_index i INNER JOIN node n ON n.nid = i.sid INNER JOIN users u ON n.uid = u.uid WHERE n.status = 0 AND (i.word LIKE 'search1' OR i.word LIKE 'search2')

table type possible_keys key key_len ref rows Extra
i range sid,word word 50 2 Using where; Using temporary
n eq_ref PRIMARY,uid,node_status_type PRIMARY 4 i.sid 1 Using where; Distinct
u eq_ref PRIMARY PRIMARY 4 n.uid 1 Using where; Using index; Distinct

EXPLAIN SELECT i.type, i.sid, SUM(i.score/t.count) AS score FROM search_index i INNER JOIN node n ON n.nid = i.sid INNER JOIN users u ON n.uid = u.uid INNER JOIN search_total t ON i.word = t.word WHERE n.status = 0 AND (i.word LIKE 'search1' OR i.word LIKE 'search2') GROUP BY i.type, i.sid ORDER BY score DESC LIMIT 0, 10

table type possible_keys key key_len ref rows Extra
i range sid,word word 50 2 Using where; Using temporary; Using filesort
n eq_ref PRIMARY,uid,node_status_type PRIMARY 4 i.sid 1 Using where
u eq_ref PRIMARY PRIMARY 4 n.uid 1 Using where; Using index
t eq_ref PRIMARY PRIMARY 50 i.word 1

EXPLAIN SELECT COUNT(a.uid) AS hits, a.uid, u.name, a.hostname, SUM(a.timer) AS total, ac.aid FROM accesslog a LEFT JOIN access ac ON ac.type = '0' AND LOWER(a.hostname) LIKE (ac.mask) LEFT JOIN users u ON a.uid = u.uid GROUP BY a.hostname ORDER BY hits DESC LIMIT 0, 10

table type possible_keys key key_len ref rows Extra
a ALL 421049 Using temporary; Using filesort
ac ALL 40
u eq_ref PRIMARY PRIMARY 4 a.uid 1

EXPLAIN SELECT DISTINCT(n.nid), n.title, n.type, n.changed, n.uid, u.name, l.last_comment_timestamp AS last_post, l.comment_count FROM node n INNER JOIN node_comment_statistics l ON n.nid = l.nid INNER JOIN users u ON n.uid = u.uid LEFT JOIN comments c ON n.nid = c.nid AND (c.status = 0 OR c.status IS NULL) WHERE n.status = 0 AND (n.uid = 0 OR c.uid = 0) ORDER BY last_post DESC LIMIT 0, 10

table type possible_keys key key_len ref rows Extra
n ref PRIMARY,uid,node_status_type node_status_type 4 const 561 Using where; Using temporary; Using filesort
l eq_ref PRIMARY PRIMARY 4 n.nid 1
u eq_ref PRIMARY PRIMARY 4 n.uid 1 Using where
c ref lid lid 4 n.nid 4 Using where; Distinct

EXPLAIN SELECT DISTINCT i.sid, i.type FROM search_index i INNER JOIN node n ON n.nid = i.sid INNER JOIN users u ON n.uid = u.uid WHERE n.status = 0 AND (i.word LIKE 'search1' OR i.word LIKE 'search2' OR i.word LIKE 'search3')

table type possible_keys key key_len ref rows Extra
i range sid,word word 50 3 Using where; Using temporary
n eq_ref PRIMARY,uid,node_status_type PRIMARY 4 i.sid 1 Using where; Distinct
u eq_ref PRIMARY PRIMARY 4 n.uid 1 Using where; Using index; Distinct

EXPLAIN SELECT i.type, i.sid, SUM(i.score/t.count) AS score FROM search_index i INNER JOIN node n ON n.nid = i.sid INNER JOIN users u ON n.uid = u.uid INNER JOIN search_total t ON i.word = t.word WHERE n.status = 0 AND (i.word LIKE 'search1' OR i.word LIKE 'search2' OR i.word LIKE 'search3') GROUP BY i.type, i.sid ORDER BY score DESC LIMIT 0, 10

table type possible_keys key key_len ref rows Extra
i range sid,word word 50 3 Using where; Using temporary; Using filesort
n eq_ref PRIMARY,uid,node_status_type PRIMARY 4 i.sid 1 Using where
u eq_ref PRIMARY PRIMARY 4 n.uid 1 Using where; Using index
t eq_ref PRIMARY PRIMARY 50 i.word 1

EXPLAIN SELECT u.*, s.* FROM users u INNER JOIN sessions s ON u.uid = s.uid WHERE s.sid = '0' AND u.status < 1 LIMIT 0, 10

Comment
Impossible WHERE noticed after reading const tables

EXPLAIN SELECT DISTINCT m.*, r.*, u.name, u.uid FROM cvs_messages m INNER JOIN cvs_files f ON m.cid = f.cid INNER JOIN cvs_repositories r ON m.rid = r.rid INNER JOIN users u ON m.uid = u.uid ORDER BY m.created DESC LIMIT 0, 10

table type possible_keys key key_len ref rows Extra
m ALL PRIMARY,uid,cid,rid 24954 Using temporary; Using filesort
f ref cid cid 4 m.cid 3 Using index
r ALL PRIMARY 2 Using where
u eq_ref PRIMARY PRIMARY 4 m.uid 1

EXPLAIN SELECT DISTINCT(n.nid), n.title, n.type, n.changed, n.uid, u.name, l.last_comment_timestamp AS last_post, l.comment_count FROM node n INNER JOIN users u ON n.uid = u.uid INNER JOIN node_comment_statistics l ON n.nid = l.nid WHERE n.status = 0 ORDER BY last_post DESC LIMIT 0, 10

table type possible_keys key key_len ref rows Extra
n ref PRIMARY,uid,node_status_type node_status_type 4 const 561 Using where; Using temporary; Using filesort
u eq_ref PRIMARY PRIMARY 4 n.uid 1 Using where
l eq_ref PRIMARY PRIMARY 4 n.nid 1

EXPLAIN SELECT COUNT(a.uid) AS hits, a.uid, u.name, a.hostname, SUM(a.timer) AS total, ac.aid FROM accesslog a LEFT JOIN access ac ON ac.type = '0' AND LOWER(a.hostname) LIKE (ac.mask) LEFT JOIN users u ON a.uid = u.uid GROUP BY a.hostname ORDER BY total DESC LIMIT 0, 10

table type possible_keys key key_len ref rows Extra
a ALL 421049 Using temporary; Using filesort
ac ALL 40
u eq_ref PRIMARY PRIMARY 4 a.uid 1

EXPLAIN SELECT COUNT(DISTINCT(n.nid)) FROM node n LEFT JOIN comments c ON n.nid = c.nid AND (c.status = 0 OR c.status IS NULL) WHERE n.status = 0 AND (n.uid = 1 OR c.uid = 1)

table type possible_keys key key_len ref rows Extra
n ref uid,node_status_type node_status_type 4 const 561 Using where
c ref lid lid 4 n.nid 4 Using where

Table structure:

DESCRIBE search_index

Field Type Null Key Default Extra
word varchar(50) MUL
sid int(10) unsigned MUL 0
type varchar(16) YES
fromsid int(10) unsigned MUL 0
fromtype varchar(16) YES
score int(10) unsigned YES

DESCRIBE node

Field Type Null Key Default Extra
nid int(10) unsigned PRI auto_increment
type varchar(32) MUL
title varchar(128) MUL
uid int(10) MUL 0
status int(4) MUL 1
created int(11) MUL 0
comment int(2) 0
promote int(2) MUL 0
moderate int(2) MUL 0
changed int(11) MUL 0
sticky int(2) 0
vid int(10) unsigned 0

DESCRIBE users

Field Type Null Key Default Extra
uid int(10) unsigned PRI 0
name varchar(60) UNI
pass varchar(32)
mail varchar(64) YES MUL
mode tinyint(1) 0
sort tinyint(1) YES 0
threshold tinyint(1) YES 0
theme varchar(255)
signature varchar(255)
access int(11) MUL 0
status tinyint(4) 0
timezone varchar(8) YES
rating decimal(6,2) YES
language varchar(12)
init varchar(64) YES
data longtext YES
created int(11) 0
picture varchar(255)
login int(11) 0

DESCRIBE search_total

Field Type Null Key Default Extra
word varchar(50) PRI
count int(10) unsigned YES

DESCRIBE cache

Field Type Null Key Default Extra
cid varchar(255) PRI
data longtext YES
expire int(11) MUL 0
created int(11) 0
headers text YES

DESCRIBE accesslog

Field Type Null Key Default Extra
aid int(10) PRI auto_increment
title varchar(255) YES
path varchar(255) YES
url varchar(255) YES
hostname varchar(128) YES
uid int(10) unsigned YES 0
timestamp int(11) unsigned MUL 0
timer int(10) unsigned 0
sid varchar(32)

DESCRIBE access

Field Type Null Key Default Extra
aid tinyint(10) PRI 0
mask varchar(255) UNI
type varchar(255)
status tinyint(2) 0

DESCRIBE comments

Field Type Null Key Default Extra
cid int(10) PRI auto_increment
pid int(10) 0
nid int(10) MUL 0
uid int(10) 0
subject varchar(64)
comment longtext YES
hostname varchar(128)
timestamp int(11) MUL 0
score mediumint(9) 0
status tinyint(3) unsigned 0
users longtext YES
thread varchar(255)
name varchar(60) YES
mail varchar(64) YES
homepage varchar(255) YES
format int(4) 0

DESCRIBE cvs_messages

Field Type Null Key Default Extra
cid int(10) unsigned PRI 0
rid int(10) unsigned MUL 0
created int(10) unsigned 0
user varchar(255)
message text YES
uid int(10) unsigned MUL 0

DESCRIBE cvs_files

Field Type Null Key Default Extra
cid int(10) unsigned MUL 0
rid int(10) unsigned MUL 0
file varchar(255) MUL
branch varchar(255) MUL
revision varchar(255)
lines_added int(10) unsigned 0
lines_removed int(10) unsigned 0
nid int(10) unsigned MUL 0
uid int(10) unsigned MUL 0

DESCRIBE cvs_repositories

Field Type Null Key Default Extra
rid int(10) unsigned PRI auto_increment
name varchar(255) UNI
root varchar(255)
modules varchar(255)
diffurl varchar(255)
newurl varchar(255)
trackerurl varchar(255)
method tinyint(2) unsigned 0
updated int(11) unsigned 0

DESCRIBE node_comment_statistics

Field Type Null Key Default Extra
nid int(10) unsigned PRI auto_increment
last_comment_timestamp int(11) MUL 0
last_comment_name varchar(60) YES
last_comment_uid int(10) 0
comment_count int(10) unsigned 0

This sections provides details about the indexes for each of the tables.
SHOW INDEX -http://dev.mysql.com/doc/refman/4.1/en/show-index.html
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type
Comment
search_index 1 sid 1 sid A 41226 NULL NULL BTREE
search_index 1 word 1 word A 204547 NULL NULL BTREE
search_index 1 fromsid 1 fromsid A 6036 NULL NULL BTREE
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
node 0 PRIMARY 1 nid A 46001 NULL NULL BTREE
node 1 uid 1 uid A 9200 NULL NULL BTREE
node 1 node_title_type 1 title A 46001 NULL NULL BTREE
node 1 node_title_type 2 type A 46001 4 NULL BTREE
node 1 node_moderate 1 moderate A 2 NULL NULL BTREE
node 1 node_promote_status 1 promote A 2 NULL NULL BTREE
node 1 node_promote_status 2 status A 5 NULL NULL BTREE
node 1 node_type 1 type A 11 10 NULL BTREE
node 1 created 1 created A 46001 NULL NULL BTREE
node 1 node_created 1 created A 46001 NULL NULL BTREE
node 1 node_changed 1 changed A 46001 NULL NULL BTREE
node 1 node_status_type 1 status A 2 NULL NULL BTREE
node 1 node_status_type 2 type A 20 NULL NULL BTREE
node 1 node_status_type 3 nid A 46001 NULL NULL BTREE
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
users 0 PRIMARY 1 uid A 48332 NULL NULL BTREE
users 0 name 1 name A 48332 NULL NULL BTREE
users 1 timestamp 1 access A 48332 NULL NULL BTREE
users 1 timestamp_2 1 access A 48332 NULL NULL BTREE
users 1 timestamp_3 1 access A 48332 NULL NULL BTREE
users 1 mail 1 mail A 48332 NULL NULL YES BTREE
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
search_total 0 PRIMARY 1 word A 186600 NULL NULL BTREE
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
cache 0 PRIMARY 1 cid A 31894 NULL NULL BTREE
cache 1 expire 1 expire A 4556 NULL NULL BTREE
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
accesslog 0 PRIMARY 1 aid A 432282 NULL NULL BTREE
accesslog 1 accesslog_timestamp 1 timestamp A 144094 NULL NULL BTREE
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
access 0 PRIMARY 1 aid A 40 NULL NULL BTREE
access 0 mask 1 mask A 40 NULL NULL BTREE
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
node_comment_statistics 0 PRIMARY 1 nid A 45984 NULL NULL BTREE
node_comment_statistics 1 node_comment_timestamp 1 last_comment_timestamp A 45984 NULL NULL BTREE
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
comments 0 PRIMARY 1 cid A 87553 NULL NULL BTREE
comments 1 lid 1 nid A 21888 NULL NULL BTREE
comments 1 timestamp 1 timestamp A 87553 NULL NULL BTREE
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
sessions 0 PRIMARY 1 sid A 512101 NULL NULL BTREE
sessions 1 uid 1 uid A 4492 NULL NULL BTREE
sessions 1 sid 1 sid A 51210 4 NULL BTREE
sessions 1 timestamp 1 timestamp A 256050 NULL NULL BTREE

Comments

Dries’s picture

Dries commented

This post seems to use invalid XHTML. Please fix.