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.campb...@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....@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.campb...@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 >
_______________________________________________ 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
