In response to Birger Blixt <[EMAIL PROTECTED]>: > Frank Sweetser wrote: > > On Thu, Sep 07, 2006 at 10:58:49AM +0200, Masopust, Christian wrote: > >> Hello Frank, > >> > >> thanks a lot for this info! but.... :-))) > >> > >> could anybody give the complete info (maybe also modify the wiki-page) how > >> these indexes should be created? > > > > The best place to find that is in the documentation for your database. That > > way you can make sure you're getting accurate information, as opposed to > > something for a previous version or the like. > > Amazing, my dbcheck at home was searching for orphaned Filename records > the whole day, and I finaly did mysaladmin kill on the query. > > Then I did: > > mysql bacula > > create index Job_FilesetId_idx on Job(FileSetId); > create index Job_ClientId_idx on Job(ClientId);
I find it hard to believe that these indexes help much, unless you've got a boatload of jobs in the system. I doubt they'll hurt anything, though. > create index File_PathId_idx on File(PathId); > create index File_FilenameId_idx on File(FilenameId); One of these two is redundant. There's already an index: "file_fp_idx" btree (filenameid, pathid) CLUSTER which should be usable by queries searching on filenameid (this is PostgreSQL, but the idea is the same) Creating another index on filenameid is just bloat and will slow down inserts. Personally, I don't recommend that anyone blindly create these indexes. As you mentioned, it took over 30 minutes to create them, which seems to indicate that their existence will have a negative impact on inserts and updates. In my case, none of these indexes made a significant improvement to dbcheck time. dbcheck took 5-1/2 minutes to run with and without the above indexes (I have about 9,000,000 rows in the file table and 500,000 in the filename table). Try them out ... if they make a significant improvement, use them. -- Bill Moran Collaborative Fusion Inc. ------------------------------------------------------------------------- Take Surveys. Earn Cash. Influence the Future of IT Join SourceForge.net's Techsay panel and you'll get the chance to share your opinions on IT & business topics through brief surveys -- and earn cash http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
