A question about queries to the TABLE_PREFIX.attachment table...

[coolegg]

I recently noticed a problem on my vBAdvanced CMPS site (vB version 3.0.5) where the index page (the CMPS portal page) was loading very slowly sometimes. Using the debug mode I was able to zero the problem down to this query here which sometimes was taking 7+ seconds to execute:

SELECT COUNT(*) AS count FROM " . TABLE_PREFIX . "attachment where visible=0

That query is used by the CMPS moderator module, which I don't use, so I was able to address the immediate problem by disabling that module (I should have done so all along since I didn't use it), and I confirmed that my normal members weren't experiencing the problem anyway because the moderator module wasn't showing for them.

But in my troubleshooting of this problem I noticed that there are several other places in several of the vB scripts where the total number of thread attachments are counted exactly as the above query does (edit_post.php, newattachment.php, function_newpost.php and so on). So I am wonderring what the root cause of the slow query is, and if it is happening elsewhere in my site (I think it is because I have noticed that saving/editting posts is also slow. I will be doing more testing tonight).

So I guess my first question is this... 1. is it normal for that query to take that long?

My second question is this... 2. When using a query like "SELECT COUNT(*) AS count FROM..." where one of the fields in the table is a large blob/text field does the server actually load up all the data from the table including the large data fields? Would I be better off if the query was "SELECT COUNT(attachmentid) AS count FROM..." where attachmentid is the primary key, would that be faster or does it not matter?

And my third question is 3. I notice that the field in the attachment table that actually holds the attachment data, "filedata", is a mediumtext field; I am curious, why is it not a blob field? Is it because some attachments are text files?

My vB site is only 1 year old; has 27,000 members, it has 1,150 attachments uploaded so far; and I limit the size to 200,000 bytes right now. My site is not really a file sharing site, but as you can see they do like to share a few files (4 per day on average, sitewide). My concern is that over time this issue is just going to get worse. So far the problem seems to be limitted to this one query which counts attachments.

Thanks in advance for any help and information.

[Andreas]

That query shouldn't take long, especially when there are only 1.5K entries.

What do you get for
[sql]EXPLAIN SELECT COUNT(*) AS count FROM attachment where visible=0[/sql]
in phpMyAdmin?

[coolegg]

Originally Posted by Andreas

What do you get for
[sql]EXPLAIN SELECT COUNT(*) AS count FROM attachment where visible=0[/sql]
in phpMyAdmin?

Thanks for the help Andreas... see the results of that query attached (screenshot)

[Andreas]

You should have an index on column visible.

(3.0.5 was a typo and you are using 3.5.0, right? 3.0.x does not have this index)

[coolegg]

Originally Posted by Andreas

You should have an index on column visible.

(3.0.5 was a typo and you are using 3.5.0, right? 3.0.x does not have this index)

No typo, I am using 3.0.x (3.0.5 is the base with the 3.0.6 - 3.0.13 security patches installed manually because the board is fairly heavily hacked up (vBAdvanced and several custom hacks)). Sorry, I should have been more clear about that.

[Andreas]

3.0.5 is very old.
You really should consider upgrading ASAP; vBAdvanced is not a show stopper here

[coolegg]

Originally Posted by Andreas

3.0.5 is very old.
You really should consider upgrading ASAP; vBAdvanced is not a show stopper here

Yes, that will be my summer project. But many of the hacks were done by a paid vB hacker (only some were done by me), so i am reluctant to pay to have the hacks done again on 3.5.x, but I think we will do it this summer.