vb.org Archive - How Do I Optimise tables!!

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

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

- - How Do I Optimise tables!! (https://vborg.vbsupport.ru/showthread.php?t=72427)

How Do I Optimise tables!!

I need this one optimising. Leading on from this conversation on vbulletin.com.

http://www.vbulletin.com/forum/showthread.php?p=771783

This got closed but the last reply was interesting:

This really is a discussion for vBulletin.org.

When you run a query, all fields in the where clause should be indexed for best speed. We don't put an index on dateline because we don't use it in the Where clauses of vBulletin.

Code:

SELECT COUNT(*) AS posts FROM post WHERE dateline >

So when you run a query such as the one you outlined above, MySQL creates a temporary table, copies all the fields from the query into it, creates a temporary index, then finds the records you need and deletes its temporary information. Otherwise this is known as a Table Scan. And it takes a lot of time. This issue is specifically why this query is not part of vBulletin.

You need to take this discussion of code modifications to vBulletin.org.

==================================================

[quote]all fields in the where clause should be indexed for best speedp/quote]

How do i do this? (index tables) as it sometimes takes up to 0.3 seconds for this to comlpete the querie!!

Thanks in advance for any help.

[QUOTE=mcyates]I need this one optimising. Leading on from this conversation on vbulletin.com.

http://www.vbulletin.com/forum/showthread.php?p=771783

This got closed but the last reply was interesting:

This really is a discussion for vBulletin.org.

When you run a query, all fields in the where clause should be indexed for best speed. We don't put an index on dateline because we don't use it in the Where clauses of vBulletin.

Code:

SELECT COUNT(*) AS posts FROM post WHERE dateline >

Quote:

all fields in the where clause should be indexed for best speedp/quote]

How do i do this? (index tables) as it sometimes takes up to 0.3 seconds for this to comlpete the querie!!

Thanks in advance for any help.

Where does this come from:

Code:

SELECT COUNT(*) AS posts FROM post WHERE dateline >

Is it actually part of vbulletin, I have 3 hacks installed, vBookie, Ucash & Ushop, Top x satats (for the last 5 posts on the indext page. Is their anyway i could speed up this.

I've remived this from global.php do i need it?

Code:

# SINCE YOUR LAST VISIT 

$getnewthread=$DB_site->query_first("SELECT COUNT(*) AS threads FROM ".TABLE_PREFIX."thread WHERE lastpost > '".$bbuserinfo['lastvisit']."'"); 

$getnewpost=$DB_site->query_first("SELECT COUNT(*) AS posts FROM ".TABLE_PREFIX."post WHERE dateline > '".$bbuserinfo['lastvisit']."'");

This is the one which is slowing the site down (only by about 0.2 seconds)

sorry i just checked this with the version of vb303 which i've just downloaded and its not on their and its nothing to do with vbookie or ucash or any other hacks "currently" on my site. I must have saved global.php with a hack already on it not knowing.

sorry

You may as well delete this thread.

X vBulletin 3.8.12 by vBS Debug 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)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (4)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:

Phrase Groups Available:

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