password_policy makes use of the mysql TEXT column type on the 'policy' database column. Because of this, database lookups that include the password_policy table are unable to use MySQL's in memory temporary tables due to the inclusion of a TEXT field, and so temporary tables must be created on disk for each of these lookups. This results in a considerable performance issue as on-disk temporary tables can have a massive effect on performance. I don't see any need for this column to be of type TEXT. Wouldn't a varchar(255) suffice? As far as I can tell, you are only storing serialised settings data in there - that shouldn't be too large (my policy certainly takes up less than 255 characters)

Please consider changing this column to varchar which will allow MySQL to make proper use of indexes and in memory temporary tables.

CommentFileSizeAuthor
#1 db_schema_fix_1.patch1.79 KBafreeman

Comments

afreeman’s picture

afreeman commented
Assigned: Unassigned » afreeman
StatusFileSize
new db_schema_fix_1.patch1.79 KB

I've verified it's possible to create strings longer than 255 characters but as of 5.0.3 mysql supports varchar() of greater than 255 characters. This patch includes a fix to the schema declaration, an update function and should also resolve #703938: password policy schema mismatch issue

afreeman’s picture

afreeman commented
Status: Active » Needs review
deekayen’s picture

deekayen commented
Status: Needs review » Needs work

Use the schema update functions instead of update_sql and add the default back in.

deekayen’s picture

deekayen commented
Status: Needs work » Fixed

I made the relevant changes and committed to HEAD.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.