Re: [Koha-devel] Other suggestions to clean DB...

[Chris Cormack]

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
address@hidden                                    www.katipo.co.nz