Hi All,
I have a very large table (8gb) with information about files, and i need to run a report against it that would would look something like this:
(select * from fs_walk_scan where file_path like '\\\\server1\\groot$\\%' order by file_size desc limit 0,30)
UNION ALL
(select * from fs_walk_scan where file_path like '\\\\server1\\hroot$\\%' order by file_size desc limit 0,30)
UNION ALL
(select * from fs_walk_scan where file_path like '\\\\server1\\iroot$\\%' order by file_size desc limit 0,30)
UNION ALL
(select * from fs_walk_scan where file_path like '\\\\server2\\froot$\\%' order by file_size desc limit 0,30)
UNION ALL
(select * from fs_walk_scan where file_path like '\\\\server2\\groot$\\%' order by file_size desc limit 0,30)
UNION ALL
(select * from fs_walk_scan where file_path like '\\\\server3\\hroot$\\%' order by file_size desc limit 0,30)
UNION ALL
(select * from fs_walk_scan where file_path like '\\\\server4\\iroot$\\%' order by file_size desc limit 0,30)
UNION ALL
(select * from fs_walk_scan where file_path like '\\\\server5\\iroot$\\%' order by file_size desc limit 0,30)
[...]
order by substring_index(file_path,'\\',4), file_size desc
This method accomplishes what I need to do: Get a list of the 30 biggest files for each volume. However, this is deathly slow, and the 'like' searches are hardcoded even though they are sitting in another table and can be gotten that way.
What I'm looking for is a way to do this without going through the huge table several times. Anyone have any ideas?
Thanks.
P.S. I cant change the structure of the huge source table in any way.
Update: There are indexes on file_path and file_size, but each one of those sub(?)queries still takes about 10 mins, and I have to do 22 minimum.