4

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?

Improve this question
3
  • If you need TEXT, use it. You need TEXT. (And you need LIKE, not =.) Commented 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 Commented Sep 3, 2015 at 8:48
  • 1
    Never, ever split a single value over multiple columns. Just increase the size of the current column. Commented Aug 4, 2016 at 14:23

1 Answer 1

Reset to default
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.

Improve this answer
1
  • 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 Commented Sep 2, 2015 at 13:57

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.