On Monday 01 September 2008 08:51:48 Kern Sibbald wrote: > Hello, > > Thanks for the patch. I see no problem applying it as it is a comment, > which is a suggestion. However, as Eric has said, it may not be such a > good idea for users to turn these indexes on since, as he points out, they > will probably leave them. > > I like Eric's suggestion that dbcheck automatically (possibly with an > option, or by requesting permission) create and then delete the indexes > that are needed for speed. > > Or perhaps we can speed up dbcheck itself. The current SQL was written by > me and is *far* from optimal. Marc Cousin has rewritten the dbcheck sql in > a script file in <bacula-source>/examples/database/dbcheck.sql, which he > says runs *much* faster. In the short run, I would recommend trying out > this script. In the long run, when some developer steps forward, or we > finish our 3.0.0 work, I would like to integrate his ideas into dbcheck. > > Please see more notes below ...
The script version won't work better with mysql without indexes. The reason is that the script works with global queries instead of many small queries like dbcheck. So PostgreSQL can optimise the query by building hash joins or merge joins. Mysql can't do that (last time I checked, at least ...), and will do nested loops between job and file for instance. And without the missing indexes, mysql will still be as slow as with dbcheck, as you'll more or less have thousands of full scans on the job table (where postgresql will do only a few to build its hash). So for dbcheck with mysql, there is no other solution than adding the missing indexes (but adding and dropping them just for the dbcheck is a good option). ------------------------------------------------------------------------- This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/ _______________________________________________ Bacula-devel mailing list Bacula-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-devel
