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.