Opened 14 years ago

Closed 14 years ago

#1146 closed enhancement (fixed)

updateindexes.sh should create indexes for foreign keys

Reported by: Nicklas Nordborg Owned by: Nicklas Nordborg
Priority: critical Milestone: BASE 2.9
Component: install Version:
Keywords: Cc:

Description

MySQL automatically generates an index on all foreign key columns, but PostgreSQL doesn't. This has a huge performance impact when the foreign key is in a big table (for example any RawData?* table). Without an index, changes (eg. deletions) in the Features table, needs to do a table scan on ALL RawData?* tables to make sure that no raw data references the feature.

See http://www.mail-archive.com/basedb-devel@lists.sourceforge.net/msg00335.html for more information about this.

I think the easiest way to make sure that a PostgreSQL installation is equivalent to a MySQL installation is to let the 'updateindexes.sh' script check if an index exists for a foreign key column. If not, the index should be created.

Change History (2)

comment:1 Changed 14 years ago by Nicklas Nordborg

Owner: changed from everyone to Nicklas Nordborg
Status: newassigned

comment:2 Changed 14 years ago by Nicklas Nordborg

Resolution: fixed
Status: assignedclosed

(In [4589]) Fixes #1146: updateindexes.sh should create indexes for foreign keys

Note: See TracTickets for help on using tickets.