MySQL varchar(255) limitation & Anomaly

MySQLmysql-5.5varchar

I have defined a column in my MySQL database as :

ALTER TABLE `my_table` CHANGE `desc` `desc` VARCHAR( 255 ) 
    CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL

However, when I enter text that is longer than 233 characters from my front end the data is truncated.

Changing the size of the column to varchar(511) has made no difference.

I counted the number of characters using PHP's strlen() function and it revealed 233 characters.

Why is MySQL doing this, and how can I save the full string?

Best Answer

MySQL is not cutting it at 233. The problem is likely in your save method which cuts it to 233 before the data even reaches MySQL.

Also, don't forget that 233 limit is not character limit, and as some character might need more han 1 byte to be stored, you might see less than 233 characters.

Also please make sure that data in MySQL server is really stored as latin1, this can be accomplished with:

show variables like 'char%';

Related Solutions

Mysql – char vs varchar in thesql

To elaborate on ypercube's correct comment, the problem is not that there is fragmentation on the VARCHAR, but in rows that contain variable length data.

If I were to have a table with these values:

+---+-------+
| i | v     |
+---+-------+
| 1 | some  |
| 2 | small |
| 3 | text  |
+---+-------+

And then issue:

UPDATE my_table SET v = 'dinosaur' WHERE i = 2

Then, depending on the storage engine, I might find that there's just no room for the word 'dinosaur' within the existing row location, since rows were written sequentially and compactly to disk. That might mean I would need to change the location of row #2, or place my 'dinosaur' text on an external location, or what have you.

On the other hand, if I were to issue:

UPDATE my_table SET v = 'a'

There would now be a lot of wasted space; I might be interested in reclaiming that space.

This is the fragmentation problem within rows.

Mysql – Why isn’t this index helping the InnoDB MySQL query

This is just a guess, as I do not have all info, but you probably would be better by doing:

EXPLAIN SELECT STRAIGHT_JOIN
    *
FROM
    tusers PARTITION (p362) tu
    JOIN users PARTITION (p362) u
      ON u.group_id=tu.group_id 
      AND tu.email_address=u.email
      AND tu.group_id = 362 
WHERE
    tu.application_id=253555;

Note the STRAIGHT_JOIN, that may not be needed -if it is needed, then I may have assumed wrongly- and the tu.group_id comparison (that, again, shouldn't be needed).

Then using the following keys:

(tu.application_id, tu.group_id, tu.email_address)
(u.group_id, u.email)

However, if the number of records to be returned is 2.5M, as your cardinality suggests, then do not expect this to be fast... this is a pure IO math.

There are many other things that clicks me as problems, but I cannot say for sure without access.

Those could be even more effective if you didn't do a SELECT *.

Another thing is that varchar(255) is usually a bad idea.

Best Answer

Related Solutions

Mysql – char vs varchar in thesql

Mysql – Why isn’t this index helping the InnoDB MySQL query

Related Question