SpotCheck-DBVersion-238

Owned by Former user (Deleted)
Last updated: Sept 07, 2010Version comment

This page lists the results of a spot check on the Mifos database structure version - 238.

AIM

See if all fields that logically support relationships have indexes on them... AKA do all the _id fields have indexes. Mingle card http://mingle.mifos.org/projects/mifos/cards/2572

Query Used

select table_name, column_name, is_nullable, column_type

from information_schema.columns

where table_schema = 'mifos'

and column_name like '%_id'

and column_name not like '%currency_id'

and column_name not like '%paid'

and column_name not like '%email_id'

and column_key not in ('pri', 'mul', 'uni')

order by table_name, column_name

Query Results

The following csv file (for importing into a spreadsheet) contains the full list of items looked with a brief analysis.

http://www.mifos.org/developers/wiki/idswithoutindexes.csv

Recommendation

Add 3 FK/Indexes.

  1. Table: CUSTOMER - Column: PARENT_CUSTOMER_ID - Although search_id is typically used for finding children there are cases where this relationship is used.
  2. Table: CUSTOMER_POSITION - Column: PARENT_CUSTOMER_ID - Used in center and group details pages. Although there are only 28k rows on the GK database, the relationship should be indexed to stop unnecessary overhead.
  3. Table: GROUP_LOAN_COUNTER - Column: GROUP_PERF_ID - Not used at GK but it would be better to add it (so its not forgotten) for those clients that may use group loans.