Long running SQL queries

Discussion of general topics related to the new version and its place in the world. Don't discuss new features, report bugs, ask for support, et cetera. Don't use this to spam for other boards or attack those boards!
Forum rules
Discussion of general topics related to the new release and its place in the world. Don't discuss new features, report bugs, ask for support, et cetera. Don't use this to spam for other boards or attack those boards!
Post Reply
jmpoure
Registered User
Posts: 81
Joined: Sun Mar 11, 2007 9:42 am

Long running SQL queries

Post by jmpoure »

Dear Friends,

I migrated from PhpBB 3.02to 3.04. The forum is +500.000 posts. I log any SQL query which lasts more than 30 ms, which is max on PostgreSQL.

Here are examples:

Code: Select all

2009-03-13 15:33:39 CET LOG:  durée : 574.715 ms, instruction : SELECT COUNT(p1.post_id) AS prev_posts
                                FROM phpbb_posts p1, phpbb_posts p2
                                WHERE p1.topic_id = 695
                                        AND p2.post_id = 744609
                                        AND p1.post_approved = 1
                                        AND p1.post_time <= p2.post_time

Code: Select all

2009-03-13 15:33:49 CET LOG:  durée : 733.267 ms, instruction : SELECT p.post_id
                FROM phpbb_posts p
                WHERE p.topic_id = 26885
                        AND p.post_approved = 1


                ORDER BY p.post_time DESC
         LIMIT 13 OFFSET 0
This kind of query can make a large forum explode.
First question: what is this query for? Why was it added?
Second question: How can we improve it?

Post Reply