-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Alan Brown wrote:
> 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)
Alan,
This is really a MySQL specific question, but can you give me a hint on
how those are created? I'm not really finding documentation on what
exactly to do (reason being the ALTER TABLE ADD INDEX documentation is
very clear, however since I don't 100% understand indexes, it's less
clear what is being indexed against what and how one types out that
command).
I'd appreciate it if you have the time to give me a little help on
creating even one of those -- that would probably get me started.
=R
- --
---- _ _ _ _ ___ _ _ _
|Y#| | | |\/| | \ |\ | | |Ryan Novosielski - Systems Programmer III
|$&| |__| | | |__/ | \| _| |[EMAIL PROTECTED] - 973/972.0922 (2-0922)
\__/ Univ. of Med. and Dent.|IST/AST - NJMS Medical Science Bldg - C630
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFGKNHcmb+gadEcsb4RAnm1AKClal6JiaKe/6rCW/cUsdSMsPANmgCg3fQJ
qvbVQWuxAl2gCW4DbpIQwuI=
=SpVB
-----END PGP SIGNATURE-----
begin:vcard
fn:Ryan Novosielski
n:Novosielski;Ryan
org:UMDNJ;IST/AST
adr;dom:MSB C630;;185 South Orange Avenue;Newark;NJ;07103
email;internet:[EMAIL PROTECTED]
title:Systems Programmer III
tel;work:(973) 972-0922
tel;fax:(973) 972-7412
tel;pager:(866) 20-UMDNJ
x-mozilla-html:FALSE
version:2.1
end:vcard
-------------------------------------------------------------------------
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
