Mysql – Which is better MySQL search strategy, varchar x 2 or text column

likeMySQL

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?

Best Answer

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.

Related Solutions

MySQL column type to boost performance

Having a VARCHAR as a part of an index would be cumbersome to manage although doable.

I would design the tables as follows

CREATE TABLE folderkeys
(
    id INT NOT NULL AUTO_INCREMENT,
    userid INT NOT NULL,
    foldername VARCHAR(260),
    PRIMARY KEY (id),
    UNIQUE KEY (userid,foldername)
) ENGINE=MyISAM;
CREATE TABLE datatable
(
    id INT NOT NULL AUTO_INCREMENT,
    folderkey_id INT NOT NULL,
    filename VARCHAR(1024),
    PRIMARY KEY (id),
    KEY (folderkey_id)
) ENGINE=MyISAM;

This will make the datatables indexes as character-compacted as possible

This will make it as easy doing queries like

INSERT IGNORE INTO folderkeys (userid,foldername) VALUES (1,'first');
SELECT id INTO @fldrkey FROM folderkeys WHERE userid=1 AND foldername='first';
INSERT INTO datatable (folderkey_id,filename) VALUES (@fldrkey,'...');

If you want the filename from userid 1 foldername 'first', you would do queries like this

SET @GivenUserID = 1;
SET @GivenFolder = 'first';
SELECT A.userid,A.foldername,B.filename FROM
(SELECT id FROM folderkeys WHERE userid = @GivenUserID AND foldername=@GivenFolder) A
INNER JOIN datatable B ON A.id = B.folderkey_id;

If you want filenames from all folders for userid 27, you would do queries like this

SET @GivenUserID = 27;
SELECT A.userid,A.foldername,B.filename FROM
(SELECT id FROM folderkeys WHERE userid = @GivenUserID) A
INNER JOIN datatable B ON A.id = B.folderkey_id;

Give it a Try !!!

I would normally recommend using InnoDB. Since you have lots of BLOB data, I'd rather not spend time trying to tune your log buffer and log files sizes. I would go with MyISAM for now.

BTW I gave you the tables with Option A in mind

Mysql – Using TEXT or even MEDIUMTEXT for all text columns in a MySQL table

Drawbacks of what you propose:

Temp tables (for complex SELECTs) cannot use MEMORY, which is faster than the fallback of MyISAM.
Storing numeric 'values' VARCHAR makes it difficult or slow to test for.
Key-value schema leads to ugly JOINs. And they are inefficient.
In InnoDB, depending on the ROW_FORMAT, TEXT fields may be stored in other blocks. This may slow you down (INSERTs and SELECTs) due to extra disk hits.

Yes/no -- the ALTER TABLE to change the width of a VARCHAR may be costly. In some very recent version, increasing the size of a VARCHAR became virtually free. (There may be more caveats.)

Best Answer

Related Solutions

MySQL column type to boost performance

Mysql – Using TEXT or even MEDIUMTEXT for all text columns in a MySQL table

Related Question