Please ensure this page remains unpublished.

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

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