maria-discuss team mailing list archive

[Peter Laursen <peter_laursen@xxxxxxxxxx>]

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@xxxxxxxxxxxx> 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@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
>
> _______________________________________________
> 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
>