How Do I Optimise tables!!

[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.

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.

[mcyates]

[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.

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.

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

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:

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.

[mcyates]

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

# 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)

[mcyates]

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.