I had the same problems with Jobs which have more than 680000 files and mysql as database. After putting an index on JobId in the File table I got an enormous speed up:
without index: # Time: 050915 15:12:42 # [EMAIL PROTECTED]: bacula[bacula] @ localhost [127.0.0.1] # Query_time: 1564 Lock_time: 0 Rows_sent: 681574 Rows_examined: 23246722 SELECT Path.Path,Filename.Name,FileIndex,JobId,LStat FROM File,Filename,Path WHERE File.JobId=1193 AND Filename.FilenameId=File.FilenameId AND Path.PathId=File.PathId; created the index with: ALTER TABLE `File` ADD INDEX(`JobId`); and did the same resore job again: # Time: 050915 16:01:09 # [EMAIL PROTECTED]: bacula[bacula] @ localhost [127.0.0.1] # Query_time: 33 Lock_time: 0 Rows_sent: 681574 Rows_examined: 2044722 SELECT Path.Path,Filename.Name,FileIndex,JobId,LStat FROM File,Filename,Path WHERE File.JobId=1193 AND Filename.FilenameId=File.FilenameId AND Path.PathId=File.PathId; that means a speedup by 47 !!! I hope that this index does not influence the backup too much. Also optimizing the File tables seems to help a lot. before optimizing the obove query took over 3500 seconds and the database was 500 Mb bigger. -- Mit freundlichen Gruessen / with kind regards Daniel Weuthen ------------------------------------------------------------------- Megabit Informationstechnik GmbH Karstr.25 41068 Moenchengladbach Tel: 02161/308980 mailto:[EMAIL PROTECTED] ftp://megabit.net Fax: 02161/3089818 mailto:[EMAIL PROTECTED] http://megabit.net -------------------------------------------------------------------
pgpFBfniYRgAW.pgp
Description: PGP signature
