I have a MySQL database for an SMS application. For the sms_msg column, I initially setup varchar(255) expecting SMS to be short anyway. Now I realize that's not enough and some messages are getting truncated.

I can simply convert the column to text type, but my next issue is when a user searches using keyword for an SMS message the database will have to run search on a text type column through millions of rows in the future, and that could be very slow.

So my strategy is to have three varchar(255) columns to support the overflow of characters like:


I'll just create logic where "if message is over 255 characters, split it and save to the extra columns." And so when I do a search I'll just do a

FROM sms_messages 
WHERE sms_msg_1 = '%dog%' OR sms_msg_2 = '%dog%' OR sms_msg_3 = '%dog%'

Is this an "OK" strategy or am I better off, performance wise, just using a single text column, specially if there's not much performance difference anyway?

  • If you need TEXT, use it. You need TEXT. (And you need LIKE, not =.)
    – Rick James
    Sep 2, 2015 at 16:21
  • if you use an MySQL version after MySQL 5.0.3 you can alter the varchar column to an maximal off 65,535 Sep 3, 2015 at 8:48
  • 1
    Never, ever split a single value over multiple columns. Just increase the size of the current column. Aug 4, 2016 at 14:23

1 Answer 1


Search using col LIKE '%xyz%' is bad on varchar and text alike. You probably want to use fulltext index which can work well with a text column.

Your proposed scheme is unnecessarily complex and you might even "split" the word you are searching for so it won't work.

  • I can handle logic to not split words, but yeh, i agree i kinda did feel it's unnecessarily complex. thanks for confirming varchar and text searches are pretty much on the same boat. i guess will have to go fulltext tnx
    – BrownChiLD
    Sep 2, 2015 at 13:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.