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 realized 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 just using single text column, performance wise, specially if there's not much performance difference anyway...

Regards

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?