vb.org Archive - Help optimizing a query

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

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

- - Help optimizing a query (https://vborg.vbsupport.ru/showthread.php?t=192172)

rossco_2005

09-28-2008 10:35 PM

Help optimizing a query

I need help with optimizing queries with indexes.
I try my best but whatever combinations I try the query is slow.
Here is an example query:

Code:

SELECT COUNT(nzb.threadid) as threads 

FROM thread as thread

LEFT JOIN nzb as nzb

        ON nzb.threadid=thread.threadid



WHERE thread.forumid IN (80,120,106,108,107)

        AND sticky = 0        

        AND visible IN (0,1,2)

Code:

SELECT COUNT(nzb.threadid) as threads 

FROM thread as thread

LEFT JOIN nzb as nzb

        ON nzb.threadid=thread.threadid



WHERE thread.forumid IN (80,120,106,108,107)

        AND sticky = 0        

        AND visible IN (0,1,2)

         AND thread.iconid IN(30,34,36,67,73,99,100,102,104,75,76,78,97,98,107,106,33)

They don't need to have the sticky or visible columns in the WHERE, that was just something I thought might help me to hit the forumid index in thread table.

Anyway what's the recommended indexes keeping in mind this query ^.

Thanks a lot for any help you can give me. :)

Gio~Logist

09-28-2008 10:52 PM

Hm, isn't the thread count stored in the forum cache array?

Why not build a foreach, using $vbulletin->forumcache?

rossco_2005

09-28-2008 11:47 PM

It's counting threads that meet a certain condition.
This is the same thing forumdisplay does only I've added the 'nzb' table and am limiting to certain iconids (I use them as categories in this script).

Amenadiel

09-29-2008 12:33 AM

I don't know if this applies to Mysql but when it comes to oracle, the where ... in clause doesn't use the index.

rossco_2005

09-29-2008 08:04 PM

Quote:

Originally Posted by Amenadiel (Post 1632665)

I don't know if this applies to Mysql but when it comes to oracle, the where ... in clause doesn't use the index.

Really?
That could just be my problem then, thanks for that tip I'll try something keeping that in mind when I get a chance...

All times are GMT. The time now is 02:57 PM.

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.02861 seconds Memory Usage 1,718KB 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 (2)bbcode_code_printable (1)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (5)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: