1

if we have table such:

create table x(
   id int primary key,
   something_else_1 int,
   something_else_2 int,
   something_else_3 int,

   char_data text, -- or varchar(1000)
);

this table will be queried on all fields except char_data.

most queries will be similar to:

select id, something_else_1
from x
where something_else_2 = 2 and something_else_3 = 5;

question is - if we have indexes etc,

what configuration will be better - text or varchar.

Just one final note -

I know I can separate this into two tables, buy separation in this case will be not the best idea, since all fields except the blob's will be something like unique index or similar.

Maxime Pacary
  • 22,336
  • 11
  • 85
  • 113
Nick
  • 9,962
  • 4
  • 42
  • 80
  • 1
    Related: http://stackoverflow.com/questions/2023481/mysql-large-varchar-vs-text http://stackoverflow.com/questions/3408930/does-anyone-have-considerable-proof-that-char-is-faster-than-varchar http://stackoverflow.com/questions/243252/how-much-more-inefficient-are-text-blobs-than-varchar-nvarchars – Maxime Pacary Jan 26 '12 at 09:50
  • Quick answer is that varchar would be faster, due to how MySQL handles TEXT/BLOB internally. In the grand scheme of things, you shouldn't notice huge performance penalties, or - you won't, unless you have really, really busy website. – N.B. Jan 26 '12 at 10:10

1 Answers1

2

this table will be queried on all fields except char_data.

Then data type of char_data has no influence over performance. Only if you select char_data it'll consume more bandwidth. Nothing else.

Its not a problem. Because you are not using in your sql. SELECT * will become slow but SELECT id, something_else_1 will not make it slow. WHERE id=2 and something_else_2=1 has no effect, but WHERE char_data like '%charsequence%'. As long as you are not searching your table with char_data you are safe.
Besides if you still want to search by char_data, you should enable full text search.

ALTER TABLE `x` ADD FULLTEXT(`char_data`);

Note: Full text search is only supported in MyISAM table engine.

Kumar V
  • 8,810
  • 9
  • 39
  • 58
Shiplu Mokaddim
  • 56,364
  • 17
  • 141
  • 187
  • but isn't text is stored "outside" the table? if this is true, then instead of text a "pointer" is stored into a table. this could mean that table record is short and probably the text version will be faster? – Nick Jan 26 '12 at 10:29
  • Yes, Its stored outside. But does it matter? You are not working with it. It wont be faster. If you work with the `char_data` then it makes sense. Even then, the performance difference will be very very little. You cant find it without heavy benchmarking. I doubt you'll ever hit that limit – Shiplu Mokaddim Jan 26 '12 at 10:49
  • I am expecting 100M records, probably more :) But from the thing you are telling me the only difference between varchar and text will be disk space. I am even thinking about compressing the field to save some space. thanks. – Nick Jan 26 '12 at 10:51