[Building Sakai] OSP matrix slow loading

[Noah Botimer]

These explain it pretty well:

http://stackoverflow.com/questions/4688599/how-can-i-add-on-delete-constraint-on-the-table

http://dev.mysql.com/doc/refman/5.5/en/alter-table.html


For example, osp_matrix_cell has one of these constraints:

show create table osp_matrix_cell;

| osp_matrix_cell | CREATE TABLE `osp_matrix_cell` (
  `id` varchar(36) NOT NULL,
  `matrix_id` varchar(36) NOT NULL,
  `wizard_page_id` varchar(36) DEFAULT NULL,
  `scaffolding_cell_id` varchar(36) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `wizard_page_id` (`wizard_page_id`),
  KEY `FK8C1D366DCD99D2B1` (`scaffolding_cell_id`),
  KEY `FK8C1D366DE4E7E6D3` (`wizard_page_id`),
  KEY `FK8C1D366D2D955C` (`matrix_id`),
  CONSTRAINT `FK8C1D366D2D955C` FOREIGN KEY (`matrix_id`) REFERENCES `osp_matrix` (`id`),
  CONSTRAINT `FK8C1D366DCD99D2B1` FOREIGN KEY (`scaffolding_cell_id`) REFERENCES `osp_scaffolding_cell` (`id`),
  CONSTRAINT `FK8C1D366DE4E7E6D3` FOREIGN KEY (`wizard_page_id`) REFERENCES `osp_wizard_page` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

Here, the matrix_id key is the one to change.

You will have to study the table structure quite well to know which to adjust. The relationships between the scaffolding, scaffolding cells, wizard pages, matrices, and matrix cells are rather complex. Do be careful -- keep a backup and test on a copy if you can.

Thanks,
-Noah

On May 20, 2013, at 9:19 AM, prabhu wrote:

> Can you guide be how to change to Cascade?
> 
> 
> On Mon, May 20, 2013 at 6:47 PM, Noah Botimer <botimer at umich.edu> wrote:
> Prabhu,
> 
> There is no way in the UI to delete individual users' matrices. If you have real data for that matrix (definition, scaffolding), you will have to delete rows in the database. The easiest way to do that would be to update the foreign key constraints to cascade deletes. There are a bunch of them, but they will be easy to find (try to delete and if it fails, update the constraint).
> 
> Because these are not usually set to cascade, they should be set back to restrict when done.
> 
> You could also do queries to identify the user cells, etc., and delete them by hand rather than using the cascade. This would certainly help you familiarize more with the tables.
> 
> Note that you will have to be quite careful to not delete data you want to keep. I would consider other options like profiling to see if you can identify the performance issue -- whether it is from missing users, retrieving extra data, excessive looping, or something else.
> 
> Thanks,
> -Noah

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20130520/1807efd7/attachment.html