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