[FIXED][3] Optimize slow attachment queries

[Albasoul]

Mysql is logging all slow queries and this is one of them:

/root/attachment.php

$getforuminfo=$DB_site->query_first("SELECT forumid".
iif($postid,',attachmentid ','')."
FROM thread,post
WHERE post.threadid=thread.threadid ".
iif($postid,"AND post.postid='$postid'","AND post.attachmentid='$attachmentid'")."
");


Anyone can give me a clue how to optimize this?


mysql reports:

# Time: 021125 19:48:15
# User@Host: forumi[forumi] @ localhost []
# Query_time: 20 Lock_time: 0 Rows_sent: 1 Rows_examined: 100718
SELECT forumid
FROM thread,post
WHERE post.threadid=thread.threadid AND post.attachmentid='108'

[Albasoul]

I am running Vbulletin 2.27 and the url http://www.forumishqiptar.com (not in english).

Thanks for your help. I will try your tip.

[Freddie Bingham]

Originally posted by Albasoul
Here is another slow query in attachment.php



# Query_time: 17 Lock_time: 0 Rows_sent: 1 Rows_examined: 108624
SELECT forumid
FROM thread,post
WHERE post.threadid=thread.threadid AND post.attachmentid='7338' Your calls to attachment.php should be in the format:

attachment.php?postid=109

and not

attachment.php?attachmentid=109

Either will work but the later is going to cause the problem that you list. What version of vBulletin are you running and what is the url to your forum?

[Albasoul]

I am running Vbulletin 2.27 and the url http://www.forumishqiptar.com (http://www.forumishqiptar.com/) (not in english). Also I am making use of the "attachmenst as files" hack. I don't know if this change is going to affect the hack.

Thanks for your help. I will try your tip.

[Freddie Bingham]

Originally posted by Albasoul
I am running Vbulletin 2.27 and the url http://www.forumishqiptar.com (http://www.forumishqiptar.com/) (not in english).

Thanks for your help. I will try your tip. Also I think the issue may be is that the url to the attachment that is found on the editpost screen is using attachmentid when it should be using postid. When you have posts in threads, do you see attachmentid= or postid= when looking at the source or link of the attachment?

Edit the editpost_attachment template and find:
attachmentid=$postinfo[attachmentid]change that to postid=$postinfo[postid]

[Albasoul]

Originally posted by freddie
Also I think the issue may be is that the url to the attachment that is found on the editpost screen is using attachmentid when it should be using postid. When you have posts in threads, do you see attachmentid= or postid= when looking at the source or link of the attachment?

Edit the editpost_attachment template and find:attachmentid=$postinfo[attachmentid]change that to attachmentid=$postinfo[postid]


I made the change to the template as you described. Now I have to monitor the mysql server if will report any slow queries of the same query.

I should let you know that I am using "attachments as file" hack, and I don't know if that causes any problems.

Thanks for your help.

[Freddie Bingham]

Originally posted by Albasoul
I made the change to the template as you described. Now I have to monitor the mysql server if will report any slow queries of the same query.

I should let you know that I am using "attachments as file" hack, and I don't know if that causes any problems.

Thanks for your help. Make sure that all references to attachment.php contain postid= and not attachmentid= and you will be ok.

[Albasoul]

Originally posted by freddie
Make sure that all references to attachment.php contain postid= and not attachmentid= and you will be ok.

A follow up on it.

I am still getting the slow queries reported, and I went on to investigate the columns under "post" table. I have a "postid" and an "attachmentid" column. Most of the attachmentid values are 0 for the records, however, there are some old thread values that hold the id of an attachment.

Is "attachmentid" needed in version 2.27? (I guess this was used in older versions)

How can I convert the old values from attachmentid to postid? Is there a query I can run to do this?

I have a lot of images in my site, don't want to do this manually.

Thanks in advance.

[Freddie Bingham]

Originally posted by Albasoul
A follow up on it.

I am still getting the slow queries reported, and I went on to investigate the columns under "post" table. I have a "postid" and an "attachmentid" column. Most of the attachmentid values are 0 for the records, however, there are some old thread values that hold the id of an attachment.

Is "attachmentid" needed in version 2.27? (I guess this was used in older versions)

How can I convert the old values from attachmentid to postid? Is there a query I can run to do this?

I have a lot of images in my site, don't want to do this manually.

Thanks in advance. At one time we referred to attachments via the attachmentid but we changed this to postid for performance reasons. We left in support for attachments to still function if referred to via attachment id though. No matter how old your threads are, if they contains attachments than the link to them is going to be postid= since that is coming from the postbit_attachment templates. Now if in the past your users chose to copy the url's to your attachments, at the time we used attachmentid= and pasted that into threads in order to display a picture from another thread, than you will still have this problem. Their is no simple way to update all of your posts to alter url's that may have been posted by your users. It can be done but it would require a complicated query.

[Albasoul]

I kind of figured that, cause I have linked some of the attachments by url instead of posting them twice. The problem now is that I have more than 100K posts and some 8k attachments. No way I can do this manually one by one :(

[fury]

Originally posted by Albasoul
I kind of figured that, cause I have linked some of the attachments by url instead of posting them twice. The problem now is that I have more than 100K posts and some 8k attachments. No way I can do this manually one by one :( I know this is an old thread, but one way to find posts using that old link form would be to do a search on "attachmentid" and select to show results as posts. It won't work if your maximum search length is less than 12, but it couldn't hurt to try.