Very Slow query - How can I find the source of it?

[mdreamer]

Hi,

I recently checked my DB and run some statistics\analytical reports and I came up with this:

### 8 Queries
### Total time: 143.073695, Average time: 17.884211875
### Taking 14.020451 , 16.494445 , 17.016344 , 18.409444 , 18.767823 , 18.886155 , 19.287588 , 20.191445 seconds to complete
### Rows analyzed 794111, 794111, 794135, 794135, 794139, 794143, 794143 and 794143
SET timestamp=XXX;
(
SELECT
a.attachmentid, a.contenttypeid, a.displayorder
FROM attachment AS a
INNER JOIN filedata AS fd ON (a.filedataid = fd.filedataid)
LEFT JOIN attachmenttype AS at ON (at.extension = fd.extension)
INNER JOIN album AS album ON (album.albumid = a.contentid)
LEFT JOIN profileblockprivacy AS pbp ON (pbp.userid = a.userid AND pbp.blockid = 'XXX')
LEFT JOIN user AS user ON (a.userid = user.userid)
WHERE
a.contenttypeid = XXX AND
(
(
a.contentid = XXX
AND
a.userid = XXX
)
OR
(
(
a.state <> 'XXX'
OR
a.userid = XXX
) AND album.state = 'XXX' AND album.state <> 'XXX' AND (a.userid = XXX OR pbp.requirement IS NULL OR pbp.requirement = XXX)
)
)
AND a.attachmentid IN (XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, X......XX)

How can I find out what causes this?

Thanks

[kh99]

Well, I'm pretty sure it's part of vbulletin. If you look in packages/vbattach/attach.php there is a function fetch_sql_ids_specific() which builds part of it, but I don't know where in the vb code that gets called or even in how many places.

[mdreamer]

How can I know what calls this function? (assuming it is it...)

[Lynne]

Get the function name and then search in your files for where the function is called.

[mdreamer]

You mean "build" the function call (with the arguments) that calls fetch_sql_ids_specific() ?

Thanks guys :)

[kh99]

I'm not sure what you mean by that. In any case I was being a bit lazy because of course it's possible to find where that function is called, but in this case a search finds 6 calls to that function, and they are in classes which means you next have to find where those classes are created and which method calls (or whatever they're called in php) result in fetch_sql_ids_specific() being called.

Another way would be to temporarily edit that file and put in code that checks for the parameters you're looking for and calls debug_backtrace() to get the call stack. Then you'd have to output it in some way (I usually create a file and write out the output, but I know there can be permission problems trying to do that depending on who your server is set up).

[mdreamer]

I'll look for the fetch_sql_ids_specific() calling in the files. I'll report my results.

[blind-eddie]

Via FTP, look in your tmp file (before public_html) you will then see the mysql_slow_queries folder. It will contain many logs with every slow querie you have ever had, all dated.

[mdreamer]

thanks
I'll check it out