On Fri, 16 Sep 2005, Kern Sibbald wrote:
For the File table in MySQL it was:
PRIMARY KEY(FileId),
INDEX (JobId, PathId, FilenameId)
For PostgreSQL it was:
primary key (fileid)
create index file_jobid_idx on file (jobid);
create index file_fp_idx on file (filenameid, pathid);
The difference was because MySQL was claimed to make good use of a multiple
key index while PostgreSQL required the jobid index to work efficiently.
However, given some of the recent discussions, I'm not 100% what is the best.
I suspect that forgetting about the FileId key, something like:
index JobId
index JobId, FilenameId, PathId
would work the best for both databases (SQLite too).
Perhaps.
FWIW here's my index stats, the numbers of entries may prove enlightening.
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
Cardinality | Sub_part | Packed | Comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+---------+
| File | 0 | PRIMARY | 1 | FileId | A |
44963272 | NULL | NULL | |
| File | 1 | JobId | 1 | JobId | A |
1374 | NULL | NULL | |
| File | 1 | PathId | 1 | PathId | A |
290085 | NULL | NULL | |
| File | 1 | FilenameId | 1 | FilenameId | A |
6423324 | NULL | NULL | |
Beyond these lines is where things get messy and it's fairly clear I'll
get some speedups by stripping it back and starting over.
The only problem is that DROP INDEX itself takes a _long_ time.
I'm surprised myself, having assumed there were about 20 million unique
filenames being backed up. One 1Tb partition alone contains about 3.5
million unique files.
-------------------------------------------------------
SF.Net email is sponsored by:
Tame your development challenges with Apache's Geronimo App Server.
Download it for free - -and be entered to win a 42" plasma tv or your very
own Sony(tm)PSP. Click here to play: http://sourceforge.net/geronimo.php
_______________________________________________
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
