Query modification to improve performance?

[Cynthia]

Our host has reviewed our server issues and advised this:

The following query is poorly optimized.

SELECT COUNT(*) AS count FROM post WHERE dateline >= 1140325200;

Since there is no index on the 'dateline' column of 'posts' mysql must read through the entire +2GB table to get the count. This datestamp represents is a relatively short range of dates so an index would be quite benificial in identifing the required posts. Currently when the count is in progress the table will be unable to be modified by another query. If an update query has priority as next query following the "select count", the table will block all other selects from completing untill the update finishes. This causes a case where lots and lots of queries which need access to the 'posts' table are stuck in the processlist. Thus making the apache process wait. This then causes the number of apache processes to build up and reach MaxClients. I believe if we add an index to 'posts.dateline' then your server will perform much better and you will have fewer issues with overload.

However the size of this table means that the process to add an index could take quite some time to complete. During this time no queries may access to table. I would like to do this modification with the forum disabled. This could be scheduled to be done at a time where it will cause the least interruption to service. Please let us know what time we could perform this maintence.

I would like to know if:

1. This modification would indeed improve performance and is good advice.
2. Considering I plan to upgrade to the most current version of vB over the next few days, would the upgrade address what the modification would, doing away with the need for the modification?

[Zachery]

Thats not a default vbulletin query, looks like one of mine, or others get the ammount of new popsts since your last visit, and I did advise adding indecies.

If you upgrade that query wouldn't be there by default anymore.

[Cynthia]

If the upgrade will resolve it then I'll simply upgrade. Thanks Zachery.

[Zachery]

Assuming you are running 2.x or 3.x, upgrading to 3.5 (or from 2.0. to 3.0) should fix the issue.

Not sure what version you are running or where its stored. if you are running 3.5.x and upgrading to 3.5.y you might need to take other action.

[Cynthia]

On second thought, I'm a bit confused. If it's not a default query, why would that query be removed by default?

[Cynthia]

We're running 3.0.10

Any idea what modification that query might be for, if it is one of yours Zachery?

[Zachery]

Hmm, check your phpinclude_start template and see what code is in there, I believe I released a template mod to do this in the 3.0.x days.

Since its not default, upgrading from 3.0.x to 3.5 would remove it, regardless if its in the files (they got overwritten) or in the phpinclude_* templates as they are no longer used.

If its in the phpinclude_* templates then its going to run if you were to upgrade to 3.0.12.

[Cynthia]

I would upgrade to 3.5.3

Thanks Zachery. I'll upgrade asap and hopefully everything will be resolved.

[RaZor Edge]

I would upgrade to 3.5.3

Thanks Zachery. I'll upgrade asap and hopefully everything will be resolved.

Hi Cynthia,

Do you have upgraded your forum? Do you still have this problem?

Seems like this query is also causing me a lot of problem... (vb 3.06)

[Cynthia]

I did upgrade and optimized our server. So far so good.

[RaZor Edge]

This particular query come from this hack:
http://www.vbulletin.org/forum/showthread.php?t=60451
([vB3 RC3] Welcome Hack v1.2)

Look at this:

 
Query:
 
    SELECT COUNT(*) AS count 
    FROM post 
    WHERE dateline > '1142475009' 
Time before: 0.19997811317444
Time after: 0.20080804824829
Time taken: 0.00082993507385254
table type possible_keys key key_len ref rows Extra 
post  ALL          293603  Using where  
 
--------------------------------------------------------------------------------
Query:
 
    SELECT COUNT(*) AS count 
    FROM thread 
    WHERE lastpost > '1142475009' 
Time before: 0.2014000415802
Time after: 0.20280599594116
Time taken: 0.0014059543609619
table type possible_keys key key_len ref rows Extra 
thread  index    forumid  10    19404  Using where; Using index

If I understand... MySQL must scan more tha 293 000 rows each time the index page is loaded. Is my logic right?

Well... if I'm right...I think that explain why MySQL is using so much CPU...

[Cynthia]

I'll let the more knoweledgable folks here offer their thoughts. I'm really out of their league in such things.