Multicolumn indices with user specified column lengths are constantly dropped and rebuilt

[djfm]

Hi, I think I got a better one this time :)

I need multicolumn indexes on TEXT fields, so I need to specify a length, like this:

var Translation = schema.define('Translation', {
    messageId:      {type: Number, dataType: 'int'},
    locale:         {type: String, length: 16},
    plurality:      {type: Number, dataType: 'int'},
    msgstr:         {type: String, dataType: 'text'}
}, {
    indexes: {
        'translation_identifier_idx': {
            columns: 'locale (16), messageId, plurality, msgstr (191)'
        },
        'msgstr_idx': {
            columns: 'msgstr (191)'
        }
    }
});

Is this the correct syntax? Seems to be so according to the README.md of this repo.

The indices here are always rebuilt on autoupdate() because the logic getting the actualIndexes from the DB doesn't take indexing length into account.

I've solved it for my use case with 2 simple changes in lib/mysql.js in MySQL.prototype.alterTable:

if (actualIndexes) {
        actualIndexes.forEach(function (i) {
            var name = i.Key_name;
            if (!ai[name]) {
                ai[name] = {
                    info: i,
                    columns: []
                };
            }
            // Add length to the Column_name if it is specified
            var aiName = i.Column_name;
            if(i.Sub_part !== null) {
                aiName = aiName + " (" + i.Sub_part + ")";
            }
            ai[name].columns[i.Seq_in_index - 1] = aiName;
        });
    }

and a bit further down:

// second: check multiple indexes
var orderMatched = true;
if (indexNames.indexOf(indexName) !== -1) {
    m.settings.indexes[indexName].columns.split(/,\s*/).forEach(function (columnName, i) {
        // If the index column in the model definition has a length, standardize the name
        // so that it matches the one retrieved from the db, i.e. 'column (length)' with
        // exactly one space before the opening parenthesis.
        var match = /^(\w+)\s*(\([^ \)]+\))$/.exec(columnName);
        if(match) {
            columnName = match[1]+" "+match[2];
        }
        if (ai[indexName].columns[i] !== columnName){
            orderMatched = false;  
        } 
    });
}

It works, but there are at least 2 drawbacks that I can see:

• users need to specify the lenght of every column that has an indexing length. This is mandatory for text fields, so that's not a problem, but it is not mandatory for varchar fields, so users must remember to do it. I mean if you don't specify length on a varchar index field the DB still records a Sub_part and the index gets rebuilt because the stored definition doesn't match the user definition.
• users need to specify a length that is actually stored by the engine. On my server, I can ask for 1024-sized text indexes but mySQL will silently store them as 191-sized indexes, so to not have my indices rebuilt everytime I need to write 191 and nothing above

If you find my approach to this issue useful I'd be more than happy to make it cleaner with your advice and PR it.

[dgsan]

This does seem like a more interesting issue. I'll have to think about it more carefully and double check whether JDB core has any conventions that might be applicable. My instinct would be to change index declarations to be more like column declarations, but that's mostly because I'm not a huge fan of declaring them all within a giant string.

But yeah, this definitely could use some improvement.