On Thu, 19 Apr 2007, Ryan Novosielski wrote: > Checking for orphaned File entries. This may take some time! > Found 103804 orphaned File records.
> ...before this begins to take an inordinate amount of time? My total DB > size is only 110MB or so -- doesn't seem like this should be a big deal > to my machine. The basic problem is that the SELECT statement used returns individual lines, then totals them, because it offers a listing of the lines found. The SELECT doesn't take very long and a SELECT COUNT(*) is even faster. Dbcheck's handling of the results is the big time eater - it's MUCH faster if there are no orphaned records. There are probably more elegant ways of handling this issue but they'd require substantial recoding of dbcheck to make separate queries for a SELECT COUNT and separate SELECT. Kern is reluctant to do this (cost/benefit) and I can understand why, however someone else could always submit an updated dbcheck which can do things better. These are my indexes. It looks like duplication but it made a significant difference in speed. index order DOES matter. mysql> show index from File; +-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | File | 0 | PRIMARY | 1 | FileId | A | 133868525 | NULL | NULL | | BTREE | | | File | 1 | JobId | 1 | JobId | A | 18 | NULL | NULL | | BTREE | | | File | 1 | JobId | 2 | PathId | A | 33467131 | NULL | NULL | | BTREE | | | File | 1 | JobId | 3 | FilenameId | A | 133868525 | NULL | NULL | | BTREE | | | File | 1 | file_jobid_idx | 1 | JobId | A | 18 | NULL | NULL | | BTREE | | | File | 1 | file_pathid_idx | 1 | PathId | A | 8924568 | NULL | NULL | | BTREE | | | File | 1 | file_filenameid_idx | 1 | FilenameId | A | 26773705 | NULL | NULL | | BTREE | | +-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 7 rows in set (1.77 sec) ------------------------------------------------------------------------- This SF.net email is sponsored by DB2 Express Download DB2 Express C - the FREE version of DB2 express and take control of your XML. No limits. Just data. Click to get it now. http://sourceforge.net/powerbar/db2/ _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
