Jump to content

User:Midom/Watchlists

From Wikipedia, the free encyclopedia

query plan for postgresql

[edit]

big watchlist, short period

[edit]
                                                         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

[edit]
                                                          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

[edit]
+----+-------------+-----------+-------+----------------------------------------+---------------+---------+------+-------+------------------------------------------------+
| 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) |
+----+-------------+-----------+-------+----------------------------------------+---------------+---------+------+-------+------------------------------------------------+