On Tue, 09 Nov 2010, Alan Brown wrote:
> >and it still takes 14 minutes to build the tree on one of our bigger clients.
> >We have 51 million entries in the file table.
> >
>
> Add individual indexes for Fileid, Jobid and Pathid
>
> Postgres will work with the combined index for individual table queries,
> but mysql won't.
The following are the indexes on the file table:
mysql> SHOW INDEXES 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 |
55861148 | NULL | NULL | | BTREE | |
| File | 1 | PathId | 1 | PathId | A |
735015 | NULL | NULL | | BTREE | |
| File | 1 | FilenameId | 1 | FilenameId | A |
2539143 | NULL | NULL | | BTREE | |
| File | 1 | FilenameId | 2 | PathId | A |
13965287 | NULL | NULL | | BTREE | |
| File | 1 | JobId | 1 | JobId | A |
1324 | NULL | NULL | | BTREE | |
| File | 1 | JobId | 2 | PathId | A |
2940060 | NULL | NULL | | BTREE | |
| File | 1 | JobId | 3 | FilenameId | A |
55861148 | NULL | NULL | | BTREE | |
| File | 1 | jobid_index | 1 | JobId | A |
1324 | NULL | NULL | | BTREE | |
| File | 1 | pathid_index | 1 | PathId | A |
735015 | NULL | NULL | | BTREE | |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
I added the last two per your instructions. Building the tree took about 14
minutes without these indexes and takes about 17-18 minutes having added
them.
Have I done something wrong? As FileId is a primary key, it doesn't seem
like I should need an extra index on that one -- is that wrong?
Thanks
Gavin
------------------------------------------------------------------------------
The Next 800 Companies to Lead America's Growth: New Video Whitepaper
David G. Thomson, author of the best-selling book "Blueprint to a
Billion" shares his insights and actions to help propel your
business during the next growth cycle. Listen Now!
http://p.sf.net/sfu/SAP-dev2dev
_______________________________________________
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
