On Tue, 2002-03-05 at 23:48, paul POULAIN wrote: I'm working on putting pk on tables in the DB. I've a few other suggestions to do : * The bibliosubject table has a no indexes. It would be useful, imho, to put one on biblionumber, and one on subject, to speed up searches. The problem is that subject is a text field (BLOB), so can't be indexed. in the sample DB, there are only 3 record being more than 80car long. And they are, (again imho) unuseable for searches : WORLD WAR, 1939-1945 - PRISONERS AND PRISONS, GERMAN - PERSONAL NARRATIVES, NEW ZEALAND, for exampl. I had divided such a subject in 3 or 4 subjects. Do you agree to modify column type to car(80), and putting an index ? You can actually index text fields (It is indexed in the hlt database in production) show index from bibliosubject; +---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+ | bibliosubject | 1 | subidx | 1 | subject | A | 17111 | 15 | NULL | | +---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+ So for subjects etc, we can just put an index on. Without needing to modify column size * The borexp (expiration date) table seems to have a problem : there should be only 1 record for 1 borrower (the pk should be on borrowernumber), and for 2 dozens of borrowers, there are 2 records. Do you confirm it's a bug in the datas ? Yep * The borrowers table has the same problem : the 1000000624,1000001224 and 1000001225 are present twice (they are test borrowers ?). If they weren't, I could put a pk on * borrowernumber field. I can correct by deleting those records Yep Heres the indices i have on borrower show index from borrowers; +-----------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +-----------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+---------+ | borrowers | 0 | PRIMARY | 1 | borrowernumber | A | NULL | NULL | NULL | | | borrowers | 1 | borrowernumber | 1 | borrowernumber | A | NULL | NULL | NULL | | | borrowers | 1 | cardnumber | 1 | cardnumber | A | NULL | NULL | NULL | | | borrowers | 1 | suridx | 1 | surname | A | NULL | 12 | NULL | | | borrowers | 1 | firstidx | 1 | firstname | A | NULL | 12 | NULL | | +-----------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+---------+ * . * The aqorderbreakdown and issues, and reserves tables should have a pk, but due to trash in sample db, the alter table does'nt work. Uncleanable by script (too many errors). I will * in a lot of tables there are columns that should be "not null" defined, and that are not. I've corrected this to enable pk-ing. Once I will have answers to those questions, the bd-patch will be released very soon (script perl), and the admin tool will follow (i can't release it immedialty, because pk is needed. But it's mostly writen) Cool. I have realised over the last few days, my production database is quite different to the sample data. It has a ton more indices for a start. But ill be keen to see the patch and see what ive missed. Chris -- Chris Cormack Programmer 025 500 789 Katipo Communications Ltd chris@katipo.co.nz www.katipo.co.nz