Re: varchar in the foodchain
On Friday 09 November 2001 12:23 am, Steve Meyers wrote: Tables with variable length rows can get ugly if not optimized frequently. We had one which was taking 2.5 sec to do an indexed query, and when we optimized the table it dropped to .2 sec. But as long as you keep your tables optimized, variable length rows can be faster. The main efficiency concern, though, is the size of your indexes. MySQL keeps your indexes in memory, so smaller indexes are better. That is why it is best to use hash values when indexing long character strings (or even fairly short ones). A 32-bit hash value of a 20-character field takes 1/5 the space. That means five times as much key can be in memory at once. Hope that makes sense... Steve Meyers Makes great sense to me, many thanks. I have often seen varchar used (on many DBs), and often wondered if it was just an easy way out. Most of what I am doing are of transactional nature vs. large char storage. For example, email addy, I've made as varchar(40). However, in light of this thread, I'll just make them char(40) and keep my indices int and bigint. The space savings and additional watchdogging for optimization, in my case do not warrant varchar (IMO). Many thanks. :-) - 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: varchar in the foodchain
On Thursday 08 November 2001 10:55 am, Paul DuBois wrote: At 10:00 AM -0500 11/8/01, Tony wrote: Does anyone know if putting (or grouping) varchar columns at the end of a table provides any performance improvements? My indices are all integers, but not have varchar columns in between several integer columns. You'll get a speed improvement only if all your columns are fixed length. Otherwise the table has variable length rows, no matter the placement of the variable length coluumns. So then is the real purpose of using varchars, to save disk space? ( I realize this is probably a general database question, just trying to learn). - 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: varchar in the foodchain
At 5:46 PM -0500 11/8/01, Tony wrote: On Thursday 08 November 2001 10:55 am, Paul DuBois wrote: At 10:00 AM -0500 11/8/01, Tony wrote: Does anyone know if putting (or grouping) varchar columns at the end of a table provides any performance improvements? My indices are all integers, but not have varchar columns in between several integer columns. You'll get a speed improvement only if all your columns are fixed length. Otherwise the table has variable length rows, no matter the placement of the variable length coluumns. So then is the real purpose of using varchars, to save disk space? ( I realize this is probably a general database question, just trying to learn). Right. - 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: varchar in the foodchain
On Thu, Nov 08, 2001 at 05:46:35PM -0500, Tony wrote: On Thursday 08 November 2001 10:55 am, Paul DuBois wrote: At 10:00 AM -0500 11/8/01, Tony wrote: Does anyone know if putting (or grouping) varchar columns at the end of a table provides any performance improvements? My indices are all integers, but not have varchar columns in between several integer columns. You'll get a speed improvement only if all your columns are fixed length. Otherwise the table has variable length rows, no matter the placement of the variable length coluumns. So then is the real purpose of using varchars, to save disk space? ( I realize this is probably a general database question, just trying to learn). + your datafile will be smaller which saves disk IO. In the end, the extra cost of the less efficient index as less than the gain from the faster access. So in the end you win speed. But, it is all explaind in the manual :) (sql, etc) -- The Moon is Waning Crescent (45% of Full) nieuw.nl - 2dehands.nl: 14523 - 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: varchar in the foodchain
So then is the real purpose of using varchars, to save disk space? ( I realize this is probably a general database question, just trying to learn). + your datafile will be smaller which saves disk IO. In the end, the extra cost of the less efficient index as less than the gain from the faster access. So in the end you win speed. huh? With a variable record length, there's a lot of searching to get the position of the individual record. With a set size, the file handler knows exactly where record n is stored in the file. This has nothing to do with file size -- disks are random access devices. But, it is all explaind in the manual :) Exactly where in the manual did you find that piece of information? / Carsten -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq - 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: varchar in the foodchain
On Fri, Nov 09, 2001 at 12:46:33AM +0100, Carsten H. Pedersen wrote: So then is the real purpose of using varchars, to save disk space? ( I realize this is probably a general database question, just trying to learn). + your datafile will be smaller which saves disk IO. In the end, the extra cost of the less efficient index as less than the gain from the faster access. So in the end you win speed. huh? With a variable record length, there's a lot of searching to get the position of the individual record. With a set size, the file handler knows exactly where record n is stored in the file. This has nothing to do with file size -- disks are random access devices. Jep, but in the end you win speed. ( Or at least: are suppoed to, your millage will vary, etc) But, it is all explaind in the manual :) Exactly where in the manual did you find that piece of information? 5.4.2 Get Your Data as Small as Possible One of the most basic optimisation is to get your data (and indexes) to take as little space on the disk (and in memory) as possible. This can give huge improvements because disk reads are faster and normally less main memory will be used. Indexing also takes less resources if done on smaller columns. .. * Use the most efficient (smallest) types possible. MySQL has many specialised types that save disk space and memory. But i read the advise literally somewhere. I'm searching.. -- The Moon is Waning Crescent (44% of Full) nieuw.nl - 2dehands.nl: 14531 - 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: varchar in the foodchain
On Fri, Nov 09, 2001 at 12:46:33AM +0100, Carsten H. Pedersen wrote: + your datafile will be smaller which saves disk IO. In the end, the extra cost of the less efficient index as less than the gain from the faster access. So in the end you win speed. huh? With a variable record length, there's a lot of searching to get the position of the individual record. With a set size, the file handler knows exactly where record n is stored in the file. This has nothing to do with file size -- disks are random access devices. But, it is all explaind in the manual :) Exactly where in the manual did you find that piece of information? found another little note: 6.5.3.1 Silent Column Specification Changes ... * If any column in a table has a variable length, the entire row is variable-length as a result. Therefore, if a table contains any variable-length columns (VARCHAR, TEXT, or BLOB), all CHAR columns longer than three characters are changed to VARCHAR columns. This doesn't affect how you use the columns in any way; in MySQL, VARCHAR is just a different way to store characters. MySQL performs this conversion because it saves space and makes table operations faster. See section 7 MySQL Table Types. Still not the advise as I rememberd it. Probably reed it on this list -- The Moon is Waning Crescent (44% of Full) nieuw.nl - 2dehands.nl: 14531 - 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: varchar in the foodchain
So then is the real purpose of using varchars, to save disk space? ( I realize this is probably a general database question, just trying to learn). + your datafile will be smaller which saves disk IO. In the end, the extra cost of the less efficient index as less than the gain from the faster access. So in the end you win speed. huh? With a variable record length, there's a lot of searching to get the position of the individual record. With a set size, the file handler knows exactly where record n is stored in the file. This has nothing to do with file size -- disks are random access devices. =if the column is indexed, then doesn't the index point at the particular block in the data file and the record's exact first-byte position within that block? - in which case there is no 'searching' of records, the index is searched and the corresponding rows retrieved (directly). =if the search is un-indexed (is that a word?) then each record will have to be visited during a SELECT, and much byte-counting as part of the searching will surely result. =dn - 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: varchar in the foodchain
It won`t alway give you better performance. But take a table like this: (id int unsigned auto_increment, emailadress char(255) ) Suppose the average emailadress is 30 characters. If you have more than a trivial amount of entrys you are wasting a lot of space. Space which all has to be cached bij your OS. Space you will all have to read from disk if you do a like search or cannot use an index or whatever. Tables with variable length rows can get ugly if not optimized frequently. We had one which was taking 2.5 sec to do an indexed query, and when we optimized the table it dropped to .2 sec. But as long as you keep your tables optimized, variable length rows can be faster. The main efficiency concern, though, is the size of your indexes. MySQL keeps your indexes in memory, so smaller indexes are better. That is why it is best to use hash values when indexing long character strings (or even fairly short ones). A 32-bit hash value of a 20-character field takes 1/5 the space. That means five times as much key can be in memory at once. Hope that makes sense... Steve Meyers - 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
