last att/first att on index

[tester]

last x attachments on index / first x attachments on index

these two scripts create huge temp files causing my mysql to pretty much lock up.

there are a lot of attachments on the board (over gb worth). this is the queries that cause the problem:

SELECT a.attach_id, a.topic_id, a.post_msg_id, a.physical_filename, a.real_filename, f.forum_id, t.topic_id, t.forum_id, t.topic_title
FROM ' . ATTACHMENTS_TABLE . ' a, ' . FORUMS_TABLE . ' f, ' . TOPICS_TABLE . ' t
WHERE (mimetype = "image/jpeg" OR mimetype = "image/png")
AND a.topic_id = t.topic_id
AND ' . $db->sql_in_set('t.forum_id', $forum_ary) . '
AND t.forum_id IN (' . $forums . ')
ORDER BY post_msg_id DESC';
$results = $db->sql_query_limit($sqls, $convert_real_size);

[4seven]

I dont noticed this, but my board is not that big. Thanks for reporting.

[tester]

Here's a very slow query:

Code: Select all

SELECT min(a.post_msg_id) as post_msg_id, a.topic_id, a.attach_id, a.physical_filename, a.real_filename, p.forum_id
    FROM    phpbb_attachments a, phpbb_posts p, phpbb_topics t
    WHERE (mimetype = "image/jpeg")
    AND   in_message = 0
    AND   p.topic_id IN ('445')
    AND   p.forum_id IN (2)
    AND   post_msg_id = topic_first_post_id
    AND   a.topic_id = p.topic_id
    GROUP BY post_msg_id DESC

Explain:

Code: Select all

| id | select_type | table | type   | possible_keys                           | key                 | key_len | ref                        | rows  | Extra                                        |

|  1 | SIMPLE      | t     | index  | topic_first_post_id                     | topic_first_post_id | 3       | NULL                       | 39471 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | p     | eq_ref | PRIMARY,forum_id,topic_id,tid_post_time | PRIMARY             | 3       | fuck.t.topic_first_post_id |     1 | Using where                                  |
|  1 | SIMPLE      | a     | ref    | topic_id,mimetype                       | topic_id            | 3       | fuck.p.topic_id            |    22 | Using where                

              |

[4seven]

Interesting.