Reason Developer Zone

Reason 4.2 includes major deep changes that eliminate some and simplify most queries in queries in Reason. In particular, we do three things:

• Queries for navigation, unique names, and relationship names are heavily cached by default, speeding up almost every page.
• The allowable relationship table is no longer included in most joins.
• Many LIKE queries have been eliminated.

If you are upgrading Reason and want to take optimal advantage of these changes, we recommend a few database indexing changes. The details will differ a little depending on what indexing you've done previously, but here is an outline:

• Run all the necessary upgrade scripts for Reason 4.2.
• Set the new setting REASON_MAINTENANCE_MODE to true, and also set DISABLE_REASON_ADMINISTRATIVE_INTERFACE to true.
• • Indexing can be quite slow and any insert or update queries may lock your database, effectively bringing the site down during indexes. Without inserts or updates, selects should be fine.
• Create a multi-column index on relationship for entity_a and type, then remove the unneeded entity_a index if it exists:
• • create index entity_a_type on relationship (entity_a,type);
  • drop index entity_a_index on relationship;
    
    
• Create a multi-column index on relationship for entity_b and type, then remove the unneeded entity_b index if it exists:
• • create index entity_b_type on relationship (entity_b,type);
  • drop index entity_b_index on relationship;
    
    
• Create a multi-column index on entity for type and state:
• • create index type_state_index on entity (type, state);
    
    
• Drop the old type index on entity if it exists:
• • drop index type_index on entity;
    
    
• Remove and recreate the unique name index on entity so that it covers the full character length:
• • drop index unique_name_index on entity;
  • create index unique_name_index on entity (unique_name);
    
    
• If there is not an index for the state column, add it:
• • create index state_index on entity (state);
    
    

After you update your code, run upgrade scripts, and get these indexes setup, you will likely see relatively dramatic performance improvements, especially if you have a large Reason database.

At Carleton, overall load on MySQL is about 10% of what it was with Reason 4.1 on release day.