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) |
+----+-------------+-----------+-------+----------------------------------------+---------------+---------+------+-------+------------------------------------------------+
