On 11.03.2016 12:59, Rhys.Campbell@swisscom.com wrote:
I think I could try this as a last resort, but it would require changes in the existing applications.
Could do it with a trigger to avoid application changes although that's something I'd probably try to avoid.
Agreed. I want to avoid this, too.
If you can afford it, then you could only index part of your VRAHCAR columns to make space for the extra field, i.e.
CREATE INDEX part_of_name ON customer (name(200), str(200), str_length);
The problem with this special application is that a) the column is VARCHAR(255), b) there are rows where the column is filled completely, i.e. where 255 chars are in the column, c) that the index is a unique one, d) 254*'a' + ' ' must (index wise) be another value than 254*'a'. Furthermore, the changes in the applications which are required for that probably are substantial. I know that this might be a special requirement, but on the other hand please see my reply to Alexander Barkov's post where I (hopefully) can explain why ignoring these trailing spaces in unique indexes is a faulty design from the beginning on, may it be standard or not. So, even if I could circumvent the problem with this special application by some mad index constructions and programming techniques, I still would really like to have a clean solution. Alexander has made me hope that he or one of his fellows might be willing to implement this, and I will try to give him arguments for doing so. Therefore, I now have two options: 1) If it will be implemented in MariaDB, I'll switch our applications from MySQL to MariaDB. 2) If it will not be implemented, I will stick with MySQL and switch all respective rows in all tables from VARCHAR(255) to VARBINARY(765). Thank you very much again, Binarus