Query optimization request

[jscieza]

Hi,

My programmer gave me the following query to get the latest 10 threads created on my forum to be listed in the sidebar of my forumhome (I'm using vB 3.8.x). Issue here is that the query is being listed as slow query (and a very slow one). Please take a look:

    # Query_time: 38  Lock_time: 1        Rows_sent: 10  Rows_examined: 82411
    SELECT thread.threadid, thread.title, thread.postuserid,     thread.postusername, thread.dateline, thread.replycount
                            FROM vb_thread AS thread
                            LEFT JOIN vb_deletionlog AS deletionlog ON     (deletionlog.primaryid = thread.threadid AND type = 'thread')
                            WHERE open <> 10
                                    AND thread.visible = 1
                                    AND deletionlog.primaryid IS NULL
                                    AND forumid NOT IN     (a list of 15 forumids goes here)
                            ORDER BY thread.dateline DESC
                            LIMIT 10;

How I can optimize that query?

Thank you,
Jonathan

[kh99]

I'm not an SQL expert or anything, but that looks pretty much like the same query that forumdisplay.php does except that, in forumdisplay it only does the deletionlog join if the user can see deletion notices. I think the same info about if the thread has been deleted or not is in the visible field (or maybe the open field).

So, assuming you never want deletion notices showing on your main page, you could try deleting the "LEFT JOIN" line and the "AND deletionlog" line and see how that works.

[Amenadiel]

you can ignore the left join. As long as you check for visible=1 you'll be already dismissing deleted threads.

along with the left join you should then remove the sentence "AND deletionlog.primaryid IS NULL"