hdparm -tT /dev/sda
/dev/sda:
Timing cached reads: 3548 MB in 2.00 seconds = 1771.32 MB/sec
Timing buffered disk reads: 10 MB in 3.17 seconds = 3.15 MB/sec
iotop -oP
I see that mysqld is producing massive amount of disk writes and reads (I always thought this is pretty normal, till Radu didn't check his machines and we compared...) - approx. 5-10 M/s...mysql
show full processlist;
| 594 | root | localhost | pluto_media | Query | 1623 | Sorting result | SELECT PK_File, Path, Filename, INode,
MAX(PSC_MOD) As CurrentDbAttrDate,
(1000000 * SUM(BOOKMARKS) + 10000 * SUM(ATTRIBUTES) + 100 * SUM(LONG_ATTRIBUTES) + SUM(PICTURES)) AS CurrentDbAttrCount,
(SUM(ATTRIBUTES) + SUM(LONG_ATTRIBUTES)) AS HasAttributes,
AttrDate AS OldDbAttrDate, AttrCount AS OldDbAttrCount, ModificationDate AS OldFileDate,
Source
FROM
(
(
SELECT PK_File, Path, Filename, INode, Source, Missing,
AttrDate, AttrCount, ModificationDate,
MAX(IF(Bookmark.psc_mod IS NULL,CAST('0000-00-00 00:00:00' AS DATE), Bookmark.psc_mod)) AS PSC_MOD,
COUNT(Bookmark.PK_Bookmark) AS BOOKMARKS,
0 AS ATTRIBUTES, 0 AS LONG_ATTRIBUTES, 0 AS PICTURES
FROM File
LEFT JOIN Bookmark ON Bookmark.FK_File = 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
GROUP BY PK_File, Path, Filename, INode, AttrDate, AttrCount, ModificationDate
)
UNION
(
SELECT PK_File, Path, Filename, INode, Source, Missing,
AttrDate, AttrCount, ModificationDate,
MAX(IF(Attribute.psc_mod IS NULL,CAST('0000-00-00 00:00:00' AS DATE), Attribute.psc_mod)) AS PSC_MOD,
0 AS BOOKMARKS,
COUNT(PK_Attribute) AS ATTRIBUTES,
0 AS LONG_ATTRIBUTES, 0 AS PICTURES
FROM File
LEFT JOIN File_Attribute ON File_Attribute.FK_File = PK_File
LEFT JOIN Attribute ON File_Attribute.FK_Attribute = PK_Attribute
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
GROUP BY PK_File, Path, Filename, INode, AttrDate, AttrCount, ModificationDate
)
UNION
(
SELECT PK_File, Path, Filename, INode, Source, Missing,
AttrDate, AttrCount, ModificationDate,
MAX(IF(LongAttribute.psc_mod IS NULL,CAST('0000-00-00 00:00:00' AS DATE), LongAttribute.psc_mod)) AS PSC_MOD,
0 AS BOOKMARKS, 0 AS ATTRIBUTES,
COUNT(PK_LongAttribute) AS LONG_ATTRIBUTES,
0 AS PICTURES
FROM File
LEFT JOIN LongAttribute ON LongAttribute.FK_File = 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
GROUP BY PK_File, Path, Filename, INode, AttrDate, AttrCount, ModificationDate
)
UNION
(
SELECT PK_File, Path, Filename, INode, Source, Missing,
AttrDate, AttrCount, ModificationDate,
MAX(IF(Picture_File.psc_mod IS NULL,CAST('0000-00-00 00:00:00' AS DATE), Picture_File.psc_mod)) AS PSC_MOD,
0 AS BOOKMARKS, 0 AS ATTRIBUTES, 0 AS LONG_ATTRIBUTES,
COUNT(FK_Picture) AS PICTURES
FROM File
LEFT JOIN Picture_File ON Picture_File.FK_File = 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
GROUP BY PK_File, Path, Filename, INode, AttrDate, AttrCount, ModificationDate
)
) AS FILE_UNION
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
GROUP BY PK_File, Path, Filename, INode, AttrDate, AttrCount, ModificationDate |
To give you an idea of how crazy of a rabbit hole, this is.. I've spent the last three and a half hours with a friend well versed in SQL. We were able to compact the query, but the results actually wound up being slower than when we started.Thom... did you have any luck there? I ran that query on my DB and I got
I could REALLY use a database designer's help here.
-Thom
3017 rows in set (0.40 sec)Obviously my media db is pretty modest but that's pretty snappy all the same... . Bulek, how big is yours and how long does it take if you run the query manually from the mysql prompt?
SELECT PK_File, Path, Filename, INode,
greatest(
IF(Bookmark.psc_mod IS NULL,CAST('0000-00-00 00:00:00' AS DATE), Bookmark.psc_mod),
IF(Attribute.psc_mod IS NULL,CAST('0000-00-00 00:00:00' AS DATE), Attribute.psc_mod),
IF(LongAttribute.psc_mod IS NULL,CAST('0000-00-00 00:00:00' AS DATE), LongAttribute.psc_mod),
IF(Picture_File.psc_mod IS NULL,CAST('0000-00-00 00:00:00' AS DATE), Picture_File.psc_mod),
CAST('0000-00-00 00:00:00' AS DATE)
) As CurrentDbAttrDate,
(1000000 * COUNT(Bookmark.PK_Bookmark) + 10000 * COUNT(Attribute.PK_Attribute) +
100 * COUNT(LongAttribute.PK_LongAttribute) + COUNT(Picture_File.FK_Picture))
AS CurrentDbAttrCount,
COUNT(Attribute.PK_Attribute)+COUNT(LongAttribute.PK_LongAttribute) AS HasAttributes,
AttrDate AS OldDbAttrDate, AttrCount AS OldDbAttrCount, ModificationDate AS OldFileDate,
Source
FROM
File
LEFT JOIN Bookmark ON Bookmark.FK_File = PK_File
LEFT JOIN File_Attribute ON File_Attribute.FK_File = PK_File
LEFT JOIN Attribute ON File_Attribute.FK_Attribute = PK_Attribute
LEFT JOIN LongAttribute ON LongAttribute.FK_File = PK_File
LEFT JOIN Picture_File ON Picture_File.FK_File = 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
GROUP BY PK_File, Path, Filename, INode, AttrDate, AttrCount, ModificationDate;
mysql> select count(*) from Picture_File;
+----------+
| 4285 |
+----------+
mysql> select count(*) from File;
+----------+
| 16715 |
+----------+
mysql> select count(*) from File_Attribute;
+----------+
| 35065 |
+----------+
mysql> select count(*) from LongAttribute;
+----------+
| 1043 |
+----------+
+----+-------------+----------------+--------+-----------------------------+---------+---------+-----------------------------------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+--------+-----------------------------+---------+---------+-----------------------------------------+-------+----------------------------------------------+
| 1 | SIMPLE | File | ALL | Path_Filename,Path,Filename | NULL | NULL | NULL | 21015 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | Bookmark | ALL | NULL | NULL | NULL | NULL | 387 | |
| 1 | SIMPLE | File_Attribute | ref | PRIMARY,FK_File | FK_File | 4 | pluto_media.File.PK_File | 2 | Using index |
| 1 | SIMPLE | Attribute | eq_ref | PRIMARY | PRIMARY | 4 | pluto_media.File_Attribute.FK_Attribute | 1 | |
| 1 | SIMPLE | LongAttribute | ALL | NULL | NULL | NULL | NULL | 262 | |
| 1 | SIMPLE | Picture_File | ref | FK_File | FK_File | 4 | pluto_media.File.PK_File | 1 | |
+----+-------------+----------------+--------+-----------------------------+---------+---------+-----------------------------------------+-------+----------------------------------------------+
mysql> select count(*) from Picture_File;
+----------+
| count(*) |
+----------+
| 154 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from File;
+----------+
| count(*) |
+----------+
| 4333 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from File_Attribute;
+----------+
| count(*) |
+----------+
| 11794 |
+----------+
1 row in set (0.01 sec)
mysql> select count(*) from LongAttribute;
+----------+
| count(*) |
+----------+
| 22 |
+----------+
1 row in set (0.00 sec)
SELECT File.PK_File, File.Path, File.Filename, File.Inode,
s.CurrentDbAttrDate, s.CurrentDbAttrCount, s.HasAttributes,
File.AttrDate AS OldDbAttrDate, File.AttrCount AS OldDbAttrCount,
File.ModificationDate AS OldFileDate, File.Source
FROM
(SELECT PK_File,
greatest(
IF(Bookmark.psc_mod IS NULL,CAST('0000-00-00 00:00:00' AS DATE), Bookmark.psc_mod),
IF(Attribute.psc_mod IS NULL,CAST('0000-00-00 00:00:00' AS DATE), Attribute.psc_mod),
IF(LongAttribute.psc_mod IS NULL,CAST('0000-00-00 00:00:00' AS DATE), LongAttribute.psc_mod),
IF(Picture_File.psc_mod IS NULL,CAST('0000-00-00 00:00:00' AS DATE), Picture_File.psc_mod),
CAST('0000-00-00 00:00:00' AS DATE)
) As CurrentDbAttrDate,
(1000000 * COUNT(Bookmark.PK_Bookmark) + 10000 * COUNT(Attribute.PK_Attribute) +
100 * COUNT(LongAttribute.PK_LongAttribute) + COUNT(Picture_File.FK_Picture))
AS CurrentDbAttrCount,
COUNT(Attribute.PK_Attribute)+COUNT(LongAttribute.PK_LongAttribute) AS HasAttributes
FROM
File
LEFT JOIN Bookmark ON Bookmark.FK_File = PK_File
LEFT JOIN File_Attribute ON File_Attribute.FK_File = PK_File
LEFT JOIN Attribute ON File_Attribute.FK_Attribute = PK_Attribute
LEFT JOIN LongAttribute ON LongAttribute.FK_File = PK_File
LEFT JOIN Picture_File ON Picture_File.FK_File = 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
GROUP BY PK_File) s
inner join File on s.PK_File=File.PK_File;
+----+-------------+----------------+--------+-----------------------------+---------+---------+-----------------------------------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+--------+-----------------------------+---------+---------+-----------------------------------------+-------+----------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 20292 | |
| 1 | PRIMARY | File | eq_ref | PRIMARY | PRIMARY | 4 | s.PK_File | 1 | |
| 2 | DERIVED | File | ALL | Path_Filename,Path,Filename | NULL | NULL | NULL | 21015 | Using where; Using temporary; Using filesort |
| 2 | DERIVED | Bookmark | ALL | NULL | NULL | NULL | NULL | 386 | |
| 2 | DERIVED | File_Attribute | ref | PRIMARY,FK_File | FK_File | 4 | pluto_media.File.PK_File | 2 | Using index |
| 2 | DERIVED | Attribute | eq_ref | PRIMARY | PRIMARY | 4 | pluto_media.File_Attribute.FK_Attribute | 1 | |
| 2 | DERIVED | LongAttribute | ALL | NULL | NULL | NULL | NULL | 262 | |
| 2 | DERIVED | Picture_File | ref | FK_File | FK_File | 4 | pluto_media.File.PK_File | 1 | |
+----+-------------+----------------+--------+-----------------------------+---------+---------+-----------------------------------------+-------+----------------------------------------------+
8 rows in set (12.18 sec)
+----+-------------+----------------+--------+--------------------+---------+---------+-----------------------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+--------+--------------------+---------+---------+-----------------------------------------+------+-------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 3017 | |
| 1 | PRIMARY | File | eq_ref | PRIMARY,FatGroupBy | PRIMARY | 4 | s.PK_File | 1 | |
| 2 | DERIVED | File | index | Path,Filename | PRIMARY | 4 | NULL | 3231 | Using where |
| 2 | DERIVED | Bookmark | ref | FK_File | FK_File | 5 | pluto_media.File.PK_File | 1 | |
| 2 | DERIVED | File_Attribute | ref | PRIMARY,FK_File | FK_File | 4 | pluto_media.File.PK_File | 2 | Using index |
| 2 | DERIVED | Attribute | eq_ref | PRIMARY | PRIMARY | 4 | pluto_media.File_Attribute.FK_Attribute | 1 | |
| 2 | DERIVED | LongAttribute | ref | FK_File | FK_File | 5 | pluto_media.File.PK_File | 1 | |
| 2 | DERIVED | Picture_File | ref | FK_File | FK_File | 4 | pluto_media.File.PK_File | 1 | |
+----+-------------+----------------+--------+--------------------+---------+---------+-----------------------------------------+------+-------------+
8 rows in set (0.10 sec)
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+--------+--------------------+---------+---------+-----------------------------------------+------+-------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 542 | |
| 1 | PRIMARY | File | eq_ref | PRIMARY,FatGroupBy | PRIMARY | 4 | s.PK_File | 1 | |
| 2 | DERIVED | File | index | Path,Filename | PRIMARY | 4 | NULL | 162 | Using where |
| 2 | DERIVED | Bookmark | ref | FK_File | FK_File | 5 | pluto_media.File.PK_File | 1 | |
| 2 | DERIVED | File_Attribute | ref | PRIMARY,FK_File | FK_File | 4 | pluto_media.File.PK_File | 3 | Using index |
| 2 | DERIVED | Attribute | eq_ref | PRIMARY | PRIMARY | 4 | pluto_media.File_Attribute.FK_Attribute | 1 | |
| 2 | DERIVED | LongAttribute | ref | FK_File | FK_File | 5 | pluto_media.File.PK_File | 1 | |
| 2 | DERIVED | Picture_File | ref | FK_File | FK_File | 4 | pluto_media.File.PK_File | 1 | |
+----+-------------+----------------+--------+--------------------+---------+---------+-----------------------------------------+------+-------------+
8 rows in set (0.02 sec)
Jamo asked me to post this link to his explain code: http://pastebin.com/jnQsJGAc
mysql> select count(*) from Picture_File;
+----------+
| count(*) |
+----------+
| 3999 |
+----------+
1 row in set (0.04 sec)
mysql> select count(*) from File;
+----------+
| count(*) |
+----------+
| 2422647 |
+----------+
1 row in set (1.37 sec)
mysql> select count(*) from File_Attribute;
+----------+
| count(*) |
+----------+
| 41181 |
+----------+
1 row in set (0.34 sec)
mysql> select count(*) from LongAttribute;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.02 sec)
CREATE INDEX IX_Bookmark_FK_File
ON Bookmark (FK_File);
CREATE INDEX IX_LongAttribute_FK_File
ON LongAttribute (FK_File);
CREATE INDEX IX_File_Missing
ON File(Missing);
CREATE INDEX ix_File_Path
ON File;
CREATE INDEX ix_File_FileName
ON File;
SELECT
File.PK_File,
File.Path,
File.Filename,
File.Inode,
s.CurrentDbAttrDate,
s.CurrentDbAttrCount,
s.HasAttributes,
File.AttrDate AS OldDbAttrDate,
File.AttrCount AS OldDbAttrCount,
File.ModificationDate AS OldFileDate,
File.Source
FROM
(
SELECT
PK_File,
greatest(
(IF(Bookmark.psc_mod IS NULL, CAST('0000-00-00 00:00:00' AS DATE),Bookmark.psc_mod)),
(IF(Attribute.psc_mod IS NULL,CAST('0000-00-00 00:00:00' AS DATE), Attribute.psc_mod)),
(IF(LongAttribute.psc_mod IS NULL,CAST('0000-00-00 00:00:00' AS DATE), LongAttribute.psc_mod)),
(IF(Picture_File.psc_mod IS NULL,CAST('0000-00-00 00:00:00' AS DATE), Picture_File.psc_mod)) ) As CurrentDbAttrDate,
(select count(*) from Bookmark where FK_File=PK_File) as BOOKMARKCOUNT,
(select count(*) from File_Attribute where FK_File=PK_File) as ACOUNT,
(select count(*) from LongAttribute where FK_File=PK_File) as LONGATTRCOUNT,
(select count(*) from Picture_File where FK_File=PK_File) as IMGCOUNT,
((1000000 * (select BOOKMARKCOUNT)) +
(10000 * (select ACOUNT)) +
(100 * (select LONGATTRCOUNT)) +
(select IMGCOUNT)) as CurrentDbAttrCount,
((select ACOUNT) + (select LONGATTRCOUNT)) as HasAttributes
FROM File
LEFT JOIN Bookmark ON Bookmark.FK_File = PK_File
LEFT JOIN File_Attribute ON File_Attribute.FK_File = PK_File
LEFT JOIN Attribute ON File_Attribute.FK_Attribute = PK_Attribute
LEFT JOIN LongAttribute ON LongAttribute.FK_File = PK_File
LEFT JOIN Picture_File ON Picture_File.FK_File = 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
GROUP BY PK_File
) s
INNER JOIN File ON s.PK_File=File.PK_File;
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
phenigma:
Ran jamo's query from your last post:
But I get this error:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON PK _Attribute=FK_Attribute WHERE FK_File=PK_File),
(select (IFNULL(MAX(LongAtt' at line 22