char vs. varchar
Hi all Which of these two tables wiil yield the best performance in a table with about 6 million entries (for rapid selects on either field): table_using_char field1 char(50), field2 char(50), filed 3 char(50), separate unique indexes on all 3 fields table_using_varchar field1 varchar(50), field2 varchar(50), field3 varchar(50), separate unique indexes on all 3 fields My gut feeling is the char table should be faster since each record will then be an exact length, but does this reasoning apply since I will be indexing each field and they are also unique (so any one select will only ever return exactly 1 or 0 results)? Thanks Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CHAR vs. VARCHAR
From: christophe barbe [EMAIL PROTECTED]
Is it then correct that VARCHAR(255) and TINYTEXT are strictly
equivalent?
I believe they work almost the same, but there may be performance implications because
they are stored differently.
My understanding is that TINYTEXT is simply a sort of BLOB with FULLTEXT indexing
capability. BLOBs are stored separate from the main record, thus an additional
re-direction is involved, which may involve moving the disk heads.
Thus, BLOBs (and TEXT) cannot be properly indexed (although TEXT can have FULLTEXT
indeces, which are more complicated than normal indeces), and thus cannot be key
fields.
I don't know this from looking at the source code or MySQL docs, just from experience
with other databases and a quick look at what is enabled via phpMyAdmin. Feel free to
correct me!
Also, I think something else written in this thread is wrong. Someone said that CHAR
is much quicker than VARCHAR when traversing records, because the VARCHAR field has to
be searched for the end of the string. AFAIK, it only has to look at the size field to
determine how to skip to the next field, which is MUCH faster than searching a string
for a terminator. This has an additional implication that VARCHAR fields are always
bigger for a given string than an appropriately sized CHAR field.
Again, corrections welcome, since this is stuff I learned from deep ponderings while
optimizing Sybase and Informix databases.
NOTE: I understand now the sql,query stuff. Strange idea.
Thanks. I needed that. :-)
Thanks to the admins for adding it, too. Spam can be a pain, but due to other
thoughtful mail I receive, I've already re-financed my house, have low long distance
rates, are on first-name basis with all the hottest {appropriate adjective here}
chicks, and have huge breasts and penis, so I have no need of further such goods and
services offered via this list... :-)
--
: Jan Steinman -- nature photography: http://www.Bytesmiths.com
: Bytesmiths -- artists' services: http://www.Bytesmiths.com/Services
: Join the forums at http://www.Bytesmiths.com/wiki
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
CHAR vs. VARCHAR
After reading the mysql documentation, I am not sure to get correctly the pros and cons of the VARCHAR type. My understanding is that it is useful when a text field has a length that may vary a lot. For example I am thinking using it for a description field where users will put nothing or a small text (for example cf protocol 43) or a bigger text. Am I right in my understanding? If yes, Is it right that using VARCHAR(255) has no influence on the database size? I mean let said that users never use more than 128 chars, would have defined the field as VARCHAR(129) be better? What are the disadvantages of using VARCHAR instead of CHAR. Is it going to be considerably slower? Also, If I want to allow users to add an optional comment for each entry of a table, is it reasonnable to use a VARCHAR so that only one byte is used when no comment are added? or should I create another table to store the comments only when defined by the user? Thanks, Christophe -- Christophe Barbé [EMAIL PROTECTED] GnuPG FingerPrint: E0F6 FADF 2A5C F072 6AF8 F67A 8F45 2F1E D72C B41E Imagination is more important than knowledge. Albert Einstein, On Science - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: CHAR vs. VARCHAR
Before I forget: SQL, QUERY ... going write a filter for these some day ... christophe barbe wrote: What are the disadvantages of using VARCHAR instead of CHAR. Is it going to be considerably slower? It really depends on your table, but if you find it slower, break down into multiple tables: create table FeedbackForm ( ID int unsigned, Name char(50), Address char(50), FeedbackCommentID int unsigned ); create table FeedbackComment ( ID int unsigned, Comment blob ); Then your searches through the FeedbackForm are good and fast, and finding the correct Feedback Comments isn't so bad either, but not _as_ fast. BTW, SQL ... QUERY ... bah ... -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: CHAR vs. VARCHAR
Le mar 08/10/2002 à 11:35, Michael T. Babcock a écrit : Before I forget: SQL, QUERY ... going write a filter for these some day ... I don't get what you mean by SQL, QUERY I know how to write a SQL QUERY. I am looking about experienced pros and cons on the use of VARCHAR. My understanding is that, except if you know that each time the text will have the same length, VARCHAR is better. This seems a bit too perfect to be true. Also I am wondering about the tradeof between using a separate table for rare comments (ie. most of the time empty) and the use of a VARCHAR which use only one byte to store an empty comment. Christophe christophe barbe wrote: What are the disadvantages of using VARCHAR instead of CHAR. Is it going to be considerably slower? It really depends on your table, but if you find it slower, break down into multiple tables: create table FeedbackForm ( ID int unsigned, Name char(50), Address char(50), FeedbackCommentID int unsigned ); create table FeedbackComment ( ID int unsigned, Comment blob ); Then your searches through the FeedbackForm are good and fast, and finding the correct Feedback Comments isn't so bad either, but not _as_ fast. BTW, SQL ... QUERY ... bah ... -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock -- Christophe Barbé [EMAIL PROTECTED] GnuPG FingerPrint: E0F6 FADF 2A5C F072 6AF8 F67A 8F45 2F1E D72C B41E - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: CHAR vs. VARCHAR
Christophe Barbe wrote: Le mar 08/10/2002 à 11:35, Michael T. Babcock a écrit : Before I forget: SQL, QUERY ... going write a filter for these some day ... I don't get what you mean by SQL, QUERY I know how to write a SQL QUERY. I was making sure I'd get past the spam filter. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: CHAR vs. VARCHAR
Christophe Barbe wrote: I am looking about experienced pros and cons on the use of VARCHAR. My understanding is that, except if you know that each time the text will have the same length, VARCHAR is better. This seems a bit too perfect to be true. You do not have to use all the chars ... if you have a field that is CHAR(100), it will always use 100 bytes, but you can insert a 12 byte value (or none) into it anyway. If you have lots of disk space, just use CHAR and ignore VARCHAR if you want. If you want to conserve disk space, VARCHAR will save you some (maybe lots). Also I am wondering about the tradeof between using a separate table for rare comments (ie. most of the time empty) and the use of a VARCHAR which use only one byte to store an empty comment. It depends on your queries; if you don't ask for the large blob often then you'll get better performance with seperate tables. Some of the gurus around here might have additional comments ... PS, SQL QUERYs are fun ... (anti-spam anti-filter) -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: CHAR vs. VARCHAR
VARCHAR basically sets a maximum length for the field and only stores the data that is entered into it, thus saving on space. The CHAR type has a fixed length, so if you set CHAR(100), 100 character worth of space will be used regardless of what the contents are. The only time you will gain a speed advantage is if you have no variable length fields in your record (varchar, text, etc.). You may notice that all your CHAR fields are changed to VARCHAR as soon as a variable length field type is added. CHAR is less efficient from a space storage point of view, but more efficient for searching and adding. It's faster because the database only has to read an offset value to get a record rather than reading parts until it finds the end of a record. Also, fixed length records will minimize fragmentation since deleted record space can be reused for new records. I always try to create fixed length records, sticking to CHAR types. I try to split out TEXT types into a separate table, although occasionally (rarely) it's not worth the extra coding effort. Hope that helps a bit. I didn't go into all the +/- off each field type, but I think those are the biggest ones. On Tuesday, October 8, 2002, at 09:50 AM, christophe barbe wrote: After reading the mysql documentation, I am not sure to get correctly the pros and cons of the VARCHAR type. My understanding is that it is useful when a text field has a length that may vary a lot. For example I am thinking using it for a description field where users will put nothing or a small text (for example cf protocol 43) or a bigger text. Am I right in my understanding? If yes, Is it right that using VARCHAR(255) has no influence on the database size? I mean let said that users never use more than 128 chars, would have defined the field as VARCHAR(129) be better? What are the disadvantages of using VARCHAR instead of CHAR. Is it going to be considerably slower? Also, If I want to allow users to add an optional comment for each entry of a table, is it reasonnable to use a VARCHAR so that only one byte is used when no comment are added? or should I create another table to store the comments only when defined by the user? Thanks, Christophe -- Christophe Barbé [EMAIL PROTECTED] GnuPG FingerPrint: E0F6 FADF 2A5C F072 6AF8 F67A 8F45 2F1E D72C B41E Imagination is more important than knowledge. Albert Einstein, On Science - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe- [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: CHAR vs. VARCHAR
Thanks, your comments are very helpful, especially the info that if I have already a not-fixed field in a table the performance cost of adding a VARCHAR (instead of a CHAR) is 0. NOTE for the Mailing-list Admins: There is a 1 hour (at least) between the post and the availability of the mail in the mailing list. Christophe Le mar 08/10/2002 à 12:06, Brent Baisley a écrit : VARCHAR basically sets a maximum length for the field and only stores the data that is entered into it, thus saving on space. The CHAR type has a fixed length, so if you set CHAR(100), 100 character worth of space will be used regardless of what the contents are. The only time you will gain a speed advantage is if you have no variable length fields in your record (varchar, text, etc.). You may notice that all your CHAR fields are changed to VARCHAR as soon as a variable length field type is added. CHAR is less efficient from a space storage point of view, but more efficient for searching and adding. It's faster because the database only has to read an offset value to get a record rather than reading parts until it finds the end of a record. Also, fixed length records will minimize fragmentation since deleted record space can be reused for new records. I always try to create fixed length records, sticking to CHAR types. I try to split out TEXT types into a separate table, although occasionally (rarely) it's not worth the extra coding effort. Hope that helps a bit. I didn't go into all the +/- off each field type, but I think those are the biggest ones. On Tuesday, October 8, 2002, at 09:50 AM, christophe barbe wrote: After reading the mysql documentation, I am not sure to get correctly the pros and cons of the VARCHAR type. My understanding is that it is useful when a text field has a length that may vary a lot. For example I am thinking using it for a description field where users will put nothing or a small text (for example cf protocol 43) or a bigger text. Am I right in my understanding? If yes, Is it right that using VARCHAR(255) has no influence on the database size? I mean let said that users never use more than 128 chars, would have defined the field as VARCHAR(129) be better? What are the disadvantages of using VARCHAR instead of CHAR. Is it going to be considerably slower? Also, If I want to allow users to add an optional comment for each entry of a table, is it reasonnable to use a VARCHAR so that only one byte is used when no comment are added? or should I create another table to store the comments only when defined by the user? Thanks, Christophe -- Christophe Barbé [EMAIL PROTECTED] GnuPG FingerPrint: E0F6 FADF 2A5C F072 6AF8 F67A 8F45 2F1E D72C B41E Imagination is more important than knowledge. Albert Einstein, On Science - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe- [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: CHAR vs. VARCHAR
Is it then correct that VARCHAR(255) and TINYTEXT are strictly equivalent? If no, where is the difference? Thanks, Christophe NOTE: I understand now the sql,query stuff. Strange idea. -- Christophe Barbé [EMAIL PROTECTED] GnuPG FingerPrint: E0F6 FADF 2A5C F072 6AF8 F67A 8F45 2F1E D72C B41E Things should be made as simple as possible, but not any simpler. -- Albert Einstein - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: CHAR vs. VARCHAR (fork)
The only time you will gain a speed advantage is if you have no variable length fields in your record (varchar, text, etc.). You may notice that all your CHAR fields are changed to VARCHAR as soon as a variable length field type is added. CHAR is less efficient from a space storage point On that note, I've often wanted to be able to do something along the lines of: ALTER TABLE foo USE CHAR; ALTER TABLE foo USE VARCHAR; ... and just port the current max-length values between the two, while changing all relevant columns. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock ... sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Char vs Varchar field types
Hi everyone, When is it appropriate to use Char instead of Varchar and vice versa? Thanks Luke mysql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Char vs Varchar field types
[EMAIL PROTECTED] wrote: When is it appropriate to use Char instead of Varchar and vice versa? Use char when you expect the length of the field to always be the same. Use varchar when you're not sure. Basically, when you're using varchar you're assigning a limit to the size of the string that can be put into the field, whereas with char you're saying that the string will always be exactly X characters long. So, for instance, it would be appropriate to store serial numbers as char, since most likely they will include alphabetical characters and will always be exactly the same length. And it would be more appropriate to store the name of the hardware component as a varchar, since that would be a different length for each record. Also: query, query, sql. Blasted filter. -- John Klein, Database Applications Developer | Omnia Mutantur, Systems Group - Harvard Law School | Nihil Interit - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Char vs Varchar field types
Hi Luke, When is it appropriate to use Char instead of Varchar and vice versa? Use CHAR for when you know in advance how many characters are going to be in that field. A good example is for MD5 hashes: they are always 32 characters long, so you can use CHAR(32). Use VARCHAR for when you don't know - names, addresses, etc. Cheers Jon mysql aol / - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Char vs Varchar field types
Luke, Wednesday, April 10, 2002, 4:18:48 PM, you wrote: LvB When is it appropriate to use Char instead of Varchar and vice versa? CHAR column type has fixed length, VARCHAR is variable-length string. CHAR column type appropriate to use when you have data with the same length. Don't forget that in the some cases CHAR may be changed to VARCHAR, look at: http://www.mysql.com/doc/S/i/Silent_column_changes.html LvB Thanks LvB Luke -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Char vs Varchar field types
It depends on who you talk to and what you want to do. varchar will use less disk space, but char is faster to access (except for InnoDB tables, where varchar has the advantage). The benefit is using char is that the rows have a fixed length so MySQL knows exactly where each row starts and can find it easier when searching on an index. But, if you have just 1 dynamic field (like a varchar), then you might as well have them all varchars, because the row length is no longer fixed. On Wednesday, April 10, 2002, at 08:18 AM, Luke van Blerk wrote: Hi everyone, When is it appropriate to use Char instead of Varchar and vice versa? Thanks Luke David Felio Software Developer Information Network of Arkansas http://www.AccessArkansas.org - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Char vs Varchar field types
also, note that char is changed to varchar in some cases, such as when there is a text field in the table. -Original Message- From: Jon Haworth [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 10, 2002 10:40 AM To: 'Luke van Blerk'; [EMAIL PROTECTED] Subject: RE: Char vs Varchar field types Hi Luke, When is it appropriate to use Char instead of Varchar and vice versa? Use CHAR for when you know in advance how many characters are going to be in that field. A good example is for MD5 hashes: they are always 32 characters long, so you can use CHAR(32). Use VARCHAR for when you don't know - names, addresses, etc. Cheers Jon mysql aol / - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
