I have a new query that also addressed the attribute counts issue with Jamo's version.
I think it is about as simplified as I can make it. I believe it was over-complicated from the beginning, at least for what the query set out to achieve.
SELECT
F.PK_File
,F.Path
,F.Filename
,F.Inode
,greatest(
IFNULL(B.max_psc_mod, CAST('0000-00-00 00:00:00' AS DATE)),
IFNULL(A.max_psc_mod, CAST('0000-00-00 00:00:00' AS DATE)),
IFNULL(LA.max_psc_mod, CAST('0000-00-00 00:00:00' AS DATE)),
IFNULL(PF.max_psc_mod, CAST('0000-00-00 00:00:00' AS DATE)),
CAST('0000-00-00 00:00:00' AS DATE)
) As CurrentDbAttrDate
,(1000000 * IFNULL(B.Bookmark_Count, 0)
+ 10000 * IFNULL(A.Attribute_Count, 0)
+ 100 * IFNULL(LA.Long_Attribute_Count, 0)
+ IFNULL(PF.Picture_Count, 0)
) AS CurrentDbAttrCount
,IFNULL(A.Attribute_Count,0)+IFNULL(LA.Long_Attribute_Count,0) AS HasAttributes
,F.AttrDate AS OldDbAttrDate
,F.AttrCount AS OldDbAttrCount
,F.ModificationDate AS OldFileDate
,F.Source
FROM
File F
LEFT JOIN (select MAX(psc_mod) as max_psc_mod, FK_File as FK_File, count(*) as Bookmark_Count from Bookmark group by FK_File) B ON B.FK_File = F.PK_File
LEFT JOIN (select MAX(Attrib.psc_mod) as max_psc_mod, count(*) as Attribute_Count, FileAttrib.FK_File as FK_File from File_Attribute FileAttrib left join Attribute Attrib ON FileAttrib.FK_Attribute = Attrib.PK_Attribute group by FK_File) A on A.FK_File = F.PK_File
LEFT JOIN (select MAX(psc_mod) as max_psc_mod, FK_File as FK_File, count(*) as Long_Attribute_Count from LongAttribute group by FK_File) LA ON LA.FK_File = F.PK_File
LEFT JOIN (select MAX(psc_mod) as max_psc_mod, FK_File as FK_File, count(*) as Picture_Count from Picture_File group by FK_File) PF ON PF.FK_File = F.PK_File
WHERE 1 = 1
AND ( Path LIKE '/home/public/data%' OR (Path ='/home/public' AND Filename = 'data' )
OR Path LIKE '/home/user_1/data%' OR (Path = '/home/user_1' AND Filename = 'data' )
OR Path LIKE '/home/user_2/data%' OR (Path = '/home/user_2' AND Filename = 'data' )
OR Path LIKE '/home/user_3/data%' OR (Path = '/home/user_3' AND Filename = 'data' )
OR Path LIKE '/home/user_4/data%' OR (Path = '/home/user_4' AND Filename = 'data' )
OR Path LIKE '/home/user_5/data%' OR (Path = '/home/user_5' AND Filename = 'data' )
OR Path LIKE '/home/user_6/data%' OR (Path = '/home/user_6' AND Filename = 'data' )
OR Path LIKE '/home/user_7/data%' OR (Path = '/home/user_7' AND Filename = 'data' )
OR Path LIKE '/home/user_8/data%' OR (Path = '/home/user_8' AND Filename = 'data' ) )
AND Missing = 0
I used phenigma's where clause for the sake of being thorough, and because it looked nice.
It compares exactly 100% against the original query in my test environment (from tschak's script).
It performs pretty well, too. Let me know how it works for you.
-buckle
Show Posts
).