How to add an index to a textarea?

How can I add an index on a field of type textarea? I have added the index with field(32). Which is the part of the field I'd like to index. However, each time we do a Quick Repair and Rebuild. Sugar finds the index but wants to remove and re-add the index.

Any thoughts?

This is in version 13

Example:

$dictionary['SchedulersJob']['indices']['idx_webhook'] = [
    'name' => 'idx_webhook',
    'type' => 'index',
    'fields' => [
        'target',
        'status',
        'data(32)',
        'deleted'
    ]
];

0 Harald Kuske over 1 year ago

Unfortunately this will happen always with the current code.

When the MysqlManager.php is reading the actual index definitions from the database it does not read the index length which is stored in the sub_part of the information_schema.statistics.

It's doable but irt requires a code fix in MysqlManager.php.

My proposal:

    protected function get_index_data($table_name = null, $index_name = null)
    {
        $filterByTable = $table_name !== null;
        $filterByIndex = $index_name !== null;

        $columns = array();
        if (!$filterByTable) {
            $columns[] = 'table_name as table_name';
        }

        if (!$filterByIndex) {
            $columns[] = 'index_name as index_name';
        }

        $columns[] = 'non_unique as non_unique';
        $columns[] = 'column_name as column_name';
//FIX1: ADD sub_part here
        $columns[] = 'sub_part as sub_part';
        
        ...
        
            $data[$table_name][$index_name]['name'] = $index_name;
            $data[$table_name][$index_name]['type'] = $type;
//FIX2: Check sub_part for values and add them to the column_name
            if ($row['sub_part']!="")
            $data[$table_name][$index_name]['fields'][] = $row['column_name'].'('.$row['sub_part'].')';
			else
            $data[$table_name][$index_name]['fields'][] = $row['column_name'];
        }

        return $data;
    }

Perhaps you can convince the products team to add this patch :-)