User:Catrope/SQL

mysql> EXPLAIN SELECT log_type, log_action, log_timestamp, page_id, log_user, log_namespace, log_title, log_comment, log_params FROM user, logging FORCE INDEX ( times ) LEFT JOIN page ON ( log_title = page_title ) AND ( log_namespace = page_namespace ) WHERE user_id = log_user AND ( log_type != 'suppress' ) ORDER BY log_timestamp DESC LIMIT 11;

++-+-++-++-+---++--+ ++-+-++-++-+---++--+ ++-+-++-++-+---++--+
 * id | select_type | table  | type   | possible_keys       | key        | key_len | ref                                                   | rows   | Extra                                        |
 * 1 | SIMPLE     | user    | index  | PRIMARY             | PRIMARY    | 4       | NULL                                                  | 149609 | Using index; Using temporary; Using filesort |
 * 1 | SIMPLE     | logging | ref    | type_time,user_time | user_time  | 4       | nlwiki.user.user_id                                   |      1 | Using where                                  |
 * 1 | SIMPLE     | page    | eq_ref | name_title          | name_title | 261     | nlwiki.logging.log_namespace,nlwiki.logging.log_title |      1 | Using index                                  |

mysql> EXPLAIN SELECT log_type, log_action, log_timestamp, page_id, log_user, log_namespace, log_title, log_comment, log_params FROM user, logging LEFT JOIN page ON ( log_title = page_title ) AND ( log_namespace = page_namespace ) WHERE user_id = log_user AND ( log_type != 'suppress' ) ORDER BY log_timestamp DESC LIMIT 11; ++-+-++-++-+---++--+ ++-+-++-++-+---++--+ ++-+-++-++-+---++--+
 * id | select_type | table  | type   | possible_keys       | key        | key_len | ref                                                   | rows   | Extra                                        |
 * 1 | SIMPLE     | user    | index  | PRIMARY             | PRIMARY    | 4       | NULL                                                  | 149609 | Using index; Using temporary; Using filesort |
 * 1 | SIMPLE     | logging | ref    | type_time,user_time | user_time  | 4       | nlwiki.user.user_id                                   |      1 | Using where                                  |
 * 1 | SIMPLE     | page    | eq_ref | name_title          | name_title | 261     | nlwiki.logging.log_namespace,nlwiki.logging.log_title |      1 | Using index                                  |

mysql> select version; +---+ +---+ +---+
 * version |
 * 5.0.51   |

mysql> EXPLAIN SELECT log_type, log_action, log_timestamp, page_id, log_user, log_namespace, log_title, log_comment, log_params FROM logging FORCE INDEX ( times ) LEFT JOIN page ON ( log_title = page_title ) AND ( log_namespace = page_namespace ) WHERE ( log_type != 'suppress' ) ORDER BY log_timestamp DESC LIMIT 11; ++-+-++---++-+---++-+ ++-+-++---++-+---++-+ ++-+-++---++-+---++-+
 * id | select_type | table  | type   | possible_keys | key        | key_len | ref                                                   | rows   | Extra                       |
 * 1 | SIMPLE     | logging | range  | type_time     | type_time  | 12      | NULL                                                  | 525948 | Using where; Using filesort |
 * 1 | SIMPLE     | page    | eq_ref | name_title    | name_title | 261     | nlwiki.logging.log_namespace,nlwiki.logging.log_title |      1 | Using index                 |

EXPLAIN SELECT log_type, log_action, log_timestamp, page_id, log_user, log_namespace, log_title, log_comment, log_params FROM logging LEFT JOIN page ON ( log_title = page_title ) AND ( log_namespace = page_namespace ) WHERE ( log_type != 'suppress' ) ORDER BY log_timestamp DESC LIMIT 11; ++-+-++---++-+---++-+ ++-+-++---++-+---++-+ ++-+-++---++-+---++-+
 * id | select_type | table  | type   | possible_keys | key        | key_len | ref                                                   | rows   | Extra                       |
 * 1 | SIMPLE     | logging | range  | type_time     | type_time  | 12      | NULL                                                  | 525948 | Using where; Using filesort |
 * 1 | SIMPLE     | page    | eq_ref | name_title    | name_title | 261     | nlwiki.logging.log_namespace,nlwiki.logging.log_title |      1 | Using index                 |