Slow upgrades for profile icons & group quotas

[Richard Mansfield]

Three recent upgrades are very slow on large databases and are in need of a bit of a rewrite:

- The upgrade to remove bad profileicon references from the user table
- The upgrade to initialise group file quotas
- The upgrade which moves existing profileicons into an 'images' folder.

https://reviews.mahara.org/#change,528

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/528
Committed: http://gitorious.org/mahara/mahara/commit/1beac3ad5123cf1e552dba740496244f1ed83b56
Submitter: Hugh Davenport (<email address hidden>)
Branch: master

commit 1beac3ad5123cf1e552dba740496244f1ed83b56
Author: Richard Mansfield <email address hidden>
Date: Fri Jul 29 11:46:29 2011 +1200

    Rewrite some slow upgrades (bug #817796)

    This is a rewrite of three recent upgrades that are very slow on
    large databases.

    Change-Id: Ic22b344ff499d317b19d6512004286321e492e6e
    Signed-off-by: Richard Mansfield <email address hidden>

[François Marier (fmarier)]

Reopening this one because it was only fixed and tested on Postgres.

Someone needs to test this using a large MySQL DB and write the equivalent optimized SQL.

[Hugh Davenport (hugh-davenport)]

I don't think we can get any more optimized for MySQL.

MySQL doesn't have UPDATE FROM sql syntax (like what was done with the postgres optimization).

Another option would be to do an UPDATE and a SELECT query inside the SET clause, but from the page http://dev.mysql.com/doc/refman/5.6/en/update.html down the bottom we have the following:
"Currently, you cannot update a table and select from the same table in a subquery."

This basically means we can't update the group table without SELECTing the needed information before hand. Doing it this way makes a bulk UPDATE statement difficult. Because of this, I think we should just keep as is for mysql upgrades.

Thoughts?

Cheers,

Hugh

[Hugh Davenport (hugh-davenport)]

Turns out you might be able to do a nested SELECT to get around that second issue, see http://www.xaprb.com/blog/2006/06/23/how-to-select-from-an-update-target-in-mysql/

[Hugh Davenport (hugh-davenport)]

https://reviews.mahara.org/908
for mysql optimized one

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/908
Committed: http://gitorious.org/mahara/mahara/commit/d41717dd4c40a9de84f19b26c2d2f3dd03cc8a8b
Submitter: Richard Mansfield (<email address hidden>)
Branch: master

commit d41717dd4c40a9de84f19b26c2d2f3dd03cc8a8b
Author: Hugh Davenport <email address hidden>
Date: Fri Dec 2 11:44:23 2011 +1300

    Rewrite slow upgrades (mysql) (bug #817796)

    Change-Id: I0fd7dfdc9540abb1359a78e2c4ac9cc099cc9576
    Signed-off-by: Hugh Davenport <email address hidden>