An idea that may work for you could be to TRIM() - http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_trim - the original varchar column into a virtual (PERSISTENT) column, and add an index on this column? Peter On Fri, Mar 11, 2016 at 12:59 PM, <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.
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);
-----Original Message----- From: Maria-discuss [mailto:maria-discuss-bounces+rhys.campbell= swisscom.com@lists.launchpad.net] On Behalf Of Binarus Sent: Friday, March 11, 2016 11:55 AM To: maria-discuss@lists.launchpad.net Subject: Re: [Maria-discuss] Collations, trailing spaces and unique indexes
On 11.03.2016 10:56, Rhys.Campbell@swisscom.com wrote:
Just a quick idea... Perhaps you can include an additional field in the unique index containing the length on the string. So
Str, str_len 'a', 1 'a ', 2
Unq index on (str, str_len).
Thanks for the suggestion. I already have read about that idea when researching the same problem for MySQL.
I think I could try this as a last resort, but it would require changes in the existing applications, and -more difficult- I should find out the maximum index length in indexes which contain several columns. As far as I know, the maximum index length with InnoDB is 767 bytes, and I already need these because my columns are mostly VARCHAR(255), charset UTF8. Now, if I add one of those columns to a unique index, thereby using all available index bytes, could I add a second column to the same index at all?
If I have to go this way as a last resort, I'll investigate ...
Binarus
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp