?
Solved

MySQL upgrade 4.1 to 5 -- conversion of TEXT to VARCHAR?

Posted on 2007-10-15
7
Medium Priority
?
1,894 Views
Last Modified: 2012-05-05
We are preparing to upgrade from 4.1 to 5. One thing I am wondering about would be the conversion of some TEXT columns to VARCAHR. We have two of tables each having three columns which are allowed to be up to 2000 characters. In 4.1, because of the limits on the size of VARCHAR, we were forced to put these into TEXT columns. With the much larger limit in 5, we could convert these to VARCHAR.

What are the advantages and disadvantages?

Having the separate files to contain the TEXT columns may have some overhead. On the other hand, putting those potentially large data fields into the main table may also have a performance hit.

These columns are sometimes used for text searches of the following type:

     WHERE col LIKE '%string%'

0
Comment
Question by:jasimon9
  • 4
  • 2
7 Comments
 
LVL 3

Accepted Solution

by:
mganesh earned 2000 total points
ID: 20078708
HI jasimon9,

It is not much of a performance hit having a varchar of 2000 as compared to that of text, where in the searches have to be done on separate files. You could expect the searches to be as fast as on a varchar of width 256 since the same internal mechanism has been extended in the engine to handle larget widths.

I have one table with a varchar 3500 with seemingly no performance hit compared to what I had in MySQL 4

Regards,
Ganesh
0
 

Author Comment

by:jasimon9
ID: 20079820
Interesting response. Sounds like you would recommend converting from TEXT to VARCHAR then.
0
 
LVL 3

Expert Comment

by:mganesh
ID: 20080044
Yes Indeed.

Infact, it is the other way. Performance of queries should improve if the data resides within the database rather than outside, as in a text file. I was trying to get some links on this for you.
Could not find many though.
Here is one:
http://www.issociate.de/board/post/282145/MSSQL_varchar_vs._text.html

-Ganesh
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
LVL 3

Expert Comment

by:mganesh
ID: 20080076
Well, that was a link on MSSQL, infact. But the same holds good for any database. External data is always slow to be handled.

One more point to add: Please make sure that you create a full text index on the varchar column (I'm sure you would have had it on the text column in your MySQL 4 table)

Here is another reference:
http://www.issociate.de/board/post/282145/MSSQL_varchar_vs._text.html

-Ganesh
0
 
LVL 3

Expert Comment

by:mganesh
ID: 20080086
Sorry for the typo. Here is the link:
http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
0
 
LVL 6

Expert Comment

by:msklizmantas
ID: 20080962
hi,

use text type and full text search instead of like ;)

regards,

m
0
 

Author Comment

by:jasimon9
ID: 20086460
msklizmantas: why use TEXT over VARCHAR?
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month14 days, 15 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question