jeremy_c

# User@Host: lifewit_lwc2[lifewit_lwc2] @ localhost []
# Query_time: 3  Lock_time: 0  Rows_sent: 9  Rows_examined: 9376

SELECT     
  c.com_id AS comment_id,     
  c.com_itemid AS post_id,     
  c.com_created AS created,     
  c.com_text AS body,     
  c.com_uid AS uid,     
  p.title AS title,     
  p.perm_title AS perm_title, 
  p.publish_date AS publish_date,     
  p.cat_id AS cat_id 
FROM     
  xoops_xoopscomments AS c,     
  xoops_weblog_post AS p 
WHERE     
  c.com_itemid = p.post_id     
  AND c.com_modid = 19     
  AND p.weblog_id = 30       
ORDER BY     
  c.com_created DESC 
LIMIT 9;

mysql> explain ........
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: p
         type: ref
possible_keys: PRIMARY,xoops_weblog_post_post_id_idx,xoops_weblog_post_weblog_id_idx,xoops_weblog_post_weblog_id_and_post_id_idx,xoops_weblog_post_weblog_id_and_status_idx,xoops_weblog_post_weblog_id_and_status_and_publish_date_idx
          key: xoops_weblog_post_weblog_id_and_post_id_idx
      key_len: 4
          ref: const
         rows: 474
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: c
         type: ref
possible_keys: com_itemid,xoops_xoopscomments_com_itemid_idx,xoops_xoopscomments_com_modid_idx,xoops_xoopscomments_com_modid_and_com_itemid_idx,xoops_xoopscomments_com_modid_and_com_itemid_and_com_created_idx
          key: com_itemid
      key_len: 3
          ref: lifewit_lwc2.p.post_id
         rows: 5
        Extra: Using where
2 rows in set (0.02 sec)