vb.org Archive - Query optimization request

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)

- - Query optimization request (https://vborg.vbsupport.ru/showthread.php?t=257672)

jscieza

01-23-2011 05:42 PM

Query optimization request

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:

Code:

    # 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	01-23-2011 06:15 PM

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

01-26-2011 01:37 AM

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"

All times are GMT. The time now is 06:50 PM.

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.00996 seconds Memory Usage 1,716KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (1)bbcode_code_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (3)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: