Ok, now dbcheck working very fast.

Summary.

CREATE INDEX idxPathId ON File (PathId);
CREATE INDEX idxFilenameId ON File (FilenameId);
CREATE INDEX idxJPF ON File (JobId, PathId, FilenameId);
CREATE INDEX idxFJ ON File (FileId,JobId);
CREATE INDEX idxPJ ON File (PathId,JobId);


--- 8) Check for orphaned File records

EXPLAIN SELECT File.FileId, Job.JobId FROM File LEFT OUTER JOIN Job ON
(File.JobId = Job.JobId) WHERE Job.JobId IS NULL;
+----+-------------+-------+--------+---------------+---------+---------+-------------------+---------+--------------------------------------+
| id | select_type | table | type   | possible_keys | key     |
key_len | ref               | rows    | Extra
      |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+---------+--------------------------------------+
|  1 | SIMPLE      | File  | index  | NULL          | idxFJ   | 8
 | NULL              | 8251910 | Using index
|
|  1 | SIMPLE      | Job   | eq_ref | PRIMARY       | PRIMARY | 4
 | bacula.File.JobId |       1 | Using where; Using index; Not exists
|
+----+-------------+-------+--------+---------------+---------+---------+-------------------+---------+--------------------------------------+


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
       |     8251910 |     NULL | NULL   |      | BTREE      |
|
| File  |          1 | JobId         |            1 | JobId       | A
       |        3638 |     NULL | NULL   |      | BTREE      |
|
| File  |          1 | idxPathId     |            1 | PathId      | A
       |       24270 |     NULL | NULL   |      | BTREE      |
|
| File  |          1 | idxFilenameId |            1 | FilenameId  | A
       |      187543 |     NULL | NULL   |      | BTREE      |
|
| File  |          1 | idxJPF        |            1 | JobId       | A
       |        3638 |     NULL | NULL   |      | BTREE      |
|
| File  |          1 | idxJPF        |            2 | PathId      | A
       |      434311 |     NULL | NULL   |      | BTREE      |
|
| File  |          1 | idxJPF        |            3 | FilenameId  | A
       |     8251910 |     NULL | NULL   |      | BTREE      |
|
| File  |          1 | idxFJ         |            1 | FileId      | A
       |     8251910 |     NULL | NULL   |      | BTREE      |
|
| File  |          1 | idxFJ         |            2 | JobId       | A
       |     8251910 |     NULL | NULL   |      | BTREE      |
|
| File  |          1 | idxPJ         |            1 | PathId      | A
       |       24270 |     NULL | NULL   |      | BTREE      |
|
| File  |          1 | idxPJ         |            2 | JobId       | A
       |      434311 |     NULL | NULL   |      | BTREE      |
|
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+


SHOW INDEX FROM Job;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type |
Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Job   |          0 | PRIMARY  |            1 | JobId       | A
  |        4590 |     NULL | NULL   |      | BTREE      |         |
| Job   |          1 | Name     |            1 | Name        | A
  |          62 |      128 | NULL   |      | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+


--- 9) Check for orphaned Path records

EXPLAIN SELECT DISTINCT Path.PathId, File.PathId FROM Path LEFT OUTER
JOIN File ON (Path.PathId = File.PathId) WHERE File.PathId IS NULL;
+----+-------------+-------+-------+-----------------+-----------+---------+--------------------+-------+--------------------------------------+
| id | select_type | table | type  | possible_keys   | key       |
key_len | ref                | rows  | Extra
     |
+----+-------------+-------+-------+-----------------+-----------+---------+--------------------+-------+--------------------------------------+
|  1 | SIMPLE      | Path  | index | NULL            | PRIMARY   | 4
    | NULL               | 35107 | Using index; Using temporary
 |
|  1 | SIMPLE      | File  | ref   | idxPathId,idxPJ | idxPathId | 4
    | bacula.Path.PathId |   340 | Using where; Using index; Not
exists |
+----+-------------+-------+-------+-----------------+-----------+---------+--------------------+-------+--------------------------------------+

-------------------------------------------------------------------------
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-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users