Data Issues

[Greg Meyer]

I have been playing around this morning trying to test for a consistent loss 
of the stats table on mysql (unsuccessful so far) and I noticed some 
anomalies in the structure of the database.  

The following fields have the UNIQUE and INDEX keys both set:

Table: directories       Field: dir
Table: podcastchannels   Field: url
Table: podcastepisodes   Field: url
Table: staistics         Field: url
Table: uniqueid          Field: uniqueid

This is unecessary on mysql since fields created as UNIQUE are automatically 
indexed (according to the docs).

Also, the podcastfolders.id field has its PRIMARY and INDEX keys also set, 
which is the same thing as above, by setting primary, you automatically get 
an index, and no INDEX key is necessary (I think).

Using the stats table as an example, here is the code that creates the table:

collectiondb.cpp lines 709 - 724
*****************************************************************************
CollectionDB::createStatsTable()
{
    // create music statistics database
    query( QString( "CREATE TABLE statistics ("
                    "url " + textColumnType() + " UNIQUE,"
                    "createdate INTEGER,"
                    "accessdate INTEGER,"
                    "percentage FLOAT,"
                    "rating INTEGER DEFAULT 0,"
                    "playcounter INTEGER );" ) );

    query( "CREATE INDEX url_stats ON statistics( url );" );
    query( "CREATE INDEX percentage_stats ON statistics( percentage );" );
    query( "CREATE INDEX rating_stats ON statistics( rating );" );
    query( "CREATE INDEX playcounter_stats ON statistics( playcounter );" );
}
*****************************************************************************

by creating the field as unique, I don't think that creating the index below 
is necessary.  What say you?
-- 
Greg