vb.org Archive

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   vB4 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=252)
-   -   slow query for thread.forumid. help me optimize (https://vborg.vbsupport.ru/showthread.php?t=265785)

ThorstenA 06-25-2011 01:30 PM
slow query for thread.forumid. help me optimize
 
This query is slow (0.15 seconds) despite of having indexes everywhere.

Code:
SELECT SQL_NO_CACHE post.postid 
FROM post
INNER JOIN thread ON(thread.threadid=post.threadid)
INNER JOIN attachment ON (post.postid = attachment.contentid)
WHERE thread.forumid = 1
These both queries are fast when removing either forumid=1 or the attachment table. Why is that? How can I rewrite my #1 query to make it faster?
Code:
SELECT SQL_NO_CACHE post.postid 
FROM post
INNER JOIN thread ON(thread.threadid=post.threadid)
INNER JOIN attachment ON (post.postid = attachment.contentid)
Code:
SELECT SQL_NO_CACHE post.postid 
FROM post
INNER JOIN thread ON(thread.threadid=post.threadid)
WHERE thread.forumid = 1
--------------- Added [DATE]1309012555[/DATE] at [TIME]1309012555[/TIME] ---------------

Found the solution. This is extremely fast. The forumid restriction in the inner join thread seems to be very good.
Code:
SELECT SQL_NO_CACHE post.postid 
FROM post
INNER JOIN (SELECT threadid FROM thread WHERE forumid=1) AS thread
ON(thread.threadid=post.threadid)
INNER JOIN attachment ON (post.postid = attachment.contentid)

Pandemikk 06-27-2011 05:17 AM
Glad you found the solution. Before you were selecting all threads and then applying the forumid restriction!

Happy coding.

p.s. you don't need those parenthesis for the on clause, in case you didn't know.


All times are GMT. The time now is 01:13 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.00985 seconds
  • Memory Usage 1,710KB
  • 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
  • (4)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