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) | ||||
| 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 | |||
| 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
Invalid XHTML
This post seems to use invalid XHTML. Please fix.