3

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:

sms_msg_1
sms_msg_2
sms_msg_3

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

SELECT * 
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 '15 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 – Raymond Nijland Sep 3 '15 at 8:48
  • 1
    Never, ever split a single value over multiple columns. Just increase the size of the current column. – a_horse_with_no_name Aug 4 '16 at 14:23
2

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 '15 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.