vb.org Archive

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)
-   -   Optimizing this SQL query - please help (https://vborg.vbsupport.ru/showthread.php?t=149523)

AlexanderT 06-12-2007 07:46 AM
Optimizing this SQL query - please help
 
Can you guys help me optimize this SQL query? It basically pulls out the latest ten threads with the condition that they are meant for the frontpage (custom column 'thread.frontpage' set to 1).

Code:
SELECT SQL_CACHE
        thread.threadid, thread.title, thread.dateline, thread.postusername, thread.postuserid, thread.replycount, thread.pollid, thread.uploadid,
        post.pagetext, post.attach, post.postid,
        forum.forumid, forum.parentid, forum.title AS forumtitle,
        editcp_uploads.dateline AS filedate, editcp_uploads.filename, editcp_uploads.filewidth AS imagewidth, editcp_uploads.fileheight AS imageheight
FROM thread AS thread
LEFT JOIN post ON(thread.firstpostid = post.postid)
LEFT JOIN forum ON(forum.forumid = thread.forumid)
LEFT JOIN editcp_uploads ON(thread.uploadid = editcp_uploads.id)
WHERE thread.frontpage = '1'
        AND thread.forumid NOT IN (0,29)
        AND thread.visible = 1
        AND post.visible = 1
        AND thread.open <> 10
ORDER BY thread.dateline DESC
LIMIT 0,10

Code:
id        select_type        table                        type                possible_keys        key                key_len        ref                                        rows        Extra
1        SIMPLE                thread                        range                forumid                        forumid        2                                                        10357        Using where; Using filesort
1        SIMPLE                forum                        eq_ref                PRIMARY                        PRIMARY        2                thread.forumid                1       
1        SIMPLE                editcp_uploads                eq_ref                PRIMARY                        PRIMARY        2                thread.uploadid                1       
1        SIMPLE                post                        eq_ref                PRIMARY                        PRIMARY        4                thread.firstpostid        1                Using where

Current indices:
Code:
Keyname          Type          Cardinality          Field
PRIMARY        PRIMARY 10382                          threadid
postuserid        INDEX        1730                          postuserid
pollid                INDEX        188                          pollid
dateline        INDEX        10382                          dateline
lastpost        INDEX        10382                          lastpost
lastposter        INDEX        1483                          lastposter
forumid        INDEX        10382                          forumid, visible, sticky, lastpost
title                FULLTEXT        1                          title

Basically I would prevent the query from going through all existing thread rows. If it's not possible to optimize this query, perhaps two more simple queries could do the trick?

Thanks!

Eikinskjaldi 06-12-2007 11:59 AM
If its in a where clause or a join clause, it should be indexed.

which is to say, all of these
thread.firstpostid
post.postid
forum.forumid
thread.forumid
thread.uploadid
editcp_uploads.id)
thread.frontpage
thread.visible
post.visible
thread.open
thread.dateline


All times are GMT. The time now is 04:43 AM.

Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2025, vBulletin Solutions Inc.

X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.02324 seconds
  • Memory Usage 1,717KB
  • 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
  • (3)bbcode_code_printable
  • (1)footer
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (6)option
  • (1)post_thanks_navbar_search
  • (1)printthread
  • (2)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