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