Reply to thread

Message

<blockquote data-quote="MJGraf" data-source="post: 1028187" data-attributes="member: 17886">Hmmm, there seems to be a lot of room for improvement.I just opened the SQLite database file with SQLiteSpy and noticed the following:Every table which has a non-int primary key, has two identical indices. I haven't checked, but I would suppose this holds true for each and every table MP2 generates. As an example:There is a table named MEDIA_ITEMS and two indices for this table:MEDIA_ITEMS_PK_IDX (based on column MEDIA_ITEM_ID) andsqlite_autoindex_MEDIA_ITEMS_1 (also based on column MEDIA_ITEM_ID)As the name of the second index implies, the index was auto generated by SQLite. And as per <a href="http://www.mail-archive.com/sqlite-users@sqlite.org/msg30516.html" target="_blank">this</a>, this is expected behaviour:I would guess, this holds true for any database system out there since the database has to check for uniqueness somehow. If so, we shouldn't generate indices for&nbsp; primary keys. If there are database systems out there that do not auto generate such an index, we should maybe extend the ISQLDatabase interface by something like[CODE]bool CreateIndexOnPrimaryKeys()[/CODE]and only let MP2 Server create an index, if this returns true.This basically means that we spend double the time necessary for creating indices right now. However, this should mainly influence inserts - and therefore the import. Not reading from the database. I'll see if I can recreate an empty database, delete one of each double indices manually and see how the import performs... If it's faster, I can maybe delete the unnecessary indices on startup. But this is of course a workaround. It would be better not to create them instead.Michael</blockquote>

[QUOTE="MJGraf, post: 1028187, member: 17886"] Hmmm, there seems to be a lot of room for improvement. I just opened the SQLite database file with SQLiteSpy and noticed the following: Every table which has a non-int primary key, has two identical indices. I haven't checked, but I would suppose this holds true for each and every table MP2 generates. As an example: There is a table named MEDIA_ITEMS and two indices for this table: MEDIA_ITEMS_PK_IDX (based on column MEDIA_ITEM_ID) and sqlite_autoindex_MEDIA_ITEMS_1 (also based on column MEDIA_ITEM_ID) As the name of the second index implies, the index was auto generated by SQLite. And as per [URL='http://www.mail-archive.com/sqlite-users@sqlite.org/msg30516.html']this[/URL], this is expected behaviour: I would guess, this holds true for any database system out there since the database has to check for uniqueness somehow. If so, we shouldn't generate indices for primary keys. If there are database systems out there that do not auto generate such an index, we should maybe extend the ISQLDatabase interface by something like [CODE]bool CreateIndexOnPrimaryKeys()[/CODE] and only let MP2 Server create an index, if this returns true. This basically means that we spend double the time necessary for creating indices right now. However, this should mainly influence inserts - and therefore the import. Not reading from the database. I'll see if I can recreate an empty database, delete one of each double indices manually and see how the import performs... If it's faster, I can maybe delete the unnecessary indices on startup. But this is of course a workaround. It would be better not to create them instead. Michael [/QUOTE]

Verification