maria-discuss team mailing list archive

[<Rhys.Campbell@xxxxxxxxxxxx>]

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@xxxxxxxxxxxxxxxxxxx] On Behalf Of Binarus
Sent: Friday, March 11, 2016 11:55 AM
To: maria-discuss@xxxxxxxxxxxxxxxxxxx
Subject: Re: [Maria-discuss] Collations, trailing spaces and unique indexes

On 11.03.2016 10:56, Rhys.Campbell@xxxxxxxxxxxx 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@xxxxxxxxxxxxxxxxxxx
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp