0

I am a little confused as to what happens in this situation. We have a column that is, say, VARCHAR(500) in SQL Server. The migration tool ( MySQL Workbench ) truncates the index to 255. This other post I found, plus many other places on Google, suggest that 255 is the limit for VARCHAR based indexes. From this post here, I see that it is the first 255 characters. But I am sort of confused as to how that works out in actual practice. In other words, if, by dumb luck, I am looking for a substring ( using the "like" clause ) that is after the 255 point in the real column, what would happen? Or what would happen if I compare the column using "=", ">", etc, but use more than 255 in comparison string. In these kind of screwy situations, does it just ignore the index, or what?

Sort of new to MySQL, been working with SQL Server for years, so just trying to understand what to expect. Thanks.

Community
  • 1
  • 1
Tony B
  • 915
  • 1
  • 9
  • 24

1 Answers1

0

The limit is applied by bytes, and the limit is 767 for InnoDB. So 255 characters is the limit if it's UTF8 (MySQL's implementation of UTF8 only supports up to 3 byte characters). If it's a single byte character set, your indexes can obviously be larger.

If the column is larger than the index, then exact matches will never be found using the index, and some table scans must be done, though it will use the index for ranges to get close.

LIKE and other comparison operators will still work normally. They just might be less performant.

Marcus Adams
  • 53,009
  • 9
  • 91
  • 143
  • Thanks. What happens if the "like" substring is outside of the first 255 characters, something like "column like '%FUN%'", where "FUN" substring is not in the first 255 characters. Would it return no matching rows in this case, or will it do a table scan? – Tony B Jan 08 '15 at 18:19
  • 1
    With `LIKE '%FUN%'`, regardless of index size, it won't be able to use the index at all and just do a table scan anyway. The index limitation doesn't even matter in that case. Perhaps you want a FULLTEXT index? – Marcus Adams Jan 08 '15 at 18:23
  • Thanks, I got it now. I sort of forgot that such "like" statements would not be able to use the index anyway, but it makes perfect sense, now that I think about it. – Tony B Jan 08 '15 at 19:04