User:Midom/Watchlists

big watchlist, short period
QUERY PLAN Sort (cost=71964.00..71975.49 rows=4596 width=75) Sort Key: cur.cur_timestamp -> Merge Join  (cost=71073.85..71684.43 rows=4596 width=75) Merge Cond: ("outer"."?column2?" = "inner"."?column10?") -> Sort  (cost=9815.73..10037.95 rows=88889 width=20) Sort Key: (watchlist.wl_title)::text -> Index Scan using idx_wl_user on watchlist  (cost=0.00..1552.91 rows=88889 width=20) Index Cond: (wl_user = 3) -> Sort  (cost=61258.12..61319.89 rows=24710 width=75) Sort Key: (cur.cur_title)::text -> Seq Scan on cur  (cost=0.00..58851.10 rows=24710 width=75) Filter: ((cur_namespace = 0) AND (cur_timestamp > '2004-05-25 00:00:00'::timestamp without time zone)) (12 rows)

small (300recs) watchlist, large period
QUERY PLAN --- Sort (cost=9.14..9.14 rows=1 width=75) Sort Key: cur.cur_timestamp -> Nested Loop  (cost=0.00..9.13 rows=1 width=75) Join Filter: (("outer".wl_namespace = "inner".cur_namespace) OR (("outer".wl_namespace + 1) = "inner".cur_namespace)) -> Index Scan using idx_wl_user on watchlist  (cost=0.00..2.01 rows=1 width=22) Index Cond: (wl_user = 69) -> Index Scan using idx_title_namespace on cur  (cost=0.00..7.10 rows=1 width=75) Index Cond: (("outer".wl_title)::text = (cur.cur_title)::text) Filter: (cur_timestamp > '2004-01-25 00:00:00'::timestamp without time zone) (9 rows)

query plan for mysql
++-+---+---++---+-+--+---++ | id | select_type | table    | type  | possible_keys                          | key           | key_len | ref  | rows  | Extra                                          | ++-+---+---++---+-+--+---++ | 1 | SIMPLE      | cur       | range | cur_name_title_timestamp,cur_timestamp | cur_timestamp |      14 | NULL | 57317 | Using where; Using temporary; Using filesort   | | 1 | SIMPLE      | watchlist | ALL   | namespace_title                        | NULL          |    NULL | NULL | 47396 | Range checked for each record (index map: 0x2) | ++-+---+---++---+-+--+---++