Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
until you have to dump it, zip it, ssh it over to another box and then import it back in That's what fag breaks are for... :-) Well, that and smoking. -- Richard Heyes HTML5 Graphing for FF, Chrome, Opera and Safari: http://www.rgraph.org (Updated January 4th) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
On Thu, 2009-01-08 at 10:51 +, Richard Heyes wrote: until you have to dump it, zip it, ssh it over to another box and then import it back in That's what fag breaks are for... :-) Well, that and smoking. So... when you're forgetful... have you ever had to bum a fag? Words have such different means depending on where you live ;) Cheers, Rob. -- http://www.interjinn.com Application and Templating Framework for PHP -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
So... when you're forgetful... have you ever had to bum a fag? All the time... :-) -- Richard Heyes HTML5 Graphing for FF, Chrome, Opera and Safari: http://www.rgraph.org (Updated January 4th) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
2009/1/8 Richard Heyes rich...@php.net: until you have to dump it, zip it, ssh it over to another box and then import it back in That's what fag breaks are for... :-) Well, that and smoking. http://xkcd.com/303/ -- Dotan Cohen http://what-is-what.com http://gibberish.co.il א-ב-ג-ד-ה-ו-ז-ח-ט-י-ך-כ-ל-ם-מ-ן-נ-ס-ע-ף-פ-ץ-צ-ק-ר-ש-ת ا-ب-ت-ث-ج-ح-خ-د-ذ-ر-ز-س-ش-ص-ض-ط-ظ-ع-غ-ف-ق-ك-ل-م-ن-ه-و-ي А-Б-В-Г-Д-Е-Ё-Ж-З-И-Й-К-Л-М-Н-О-П-Р-С-Т-У-Ф-Х-Ц-Ч-Ш-Щ-Ъ-Ы-Ь-Э-Ю-Я а-б-в-г-д-е-ё-ж-з-и-й-к-л-м-н-о-п-р-с-т-у-ф-х-ц-ч-ш-щ-ъ-ы-ь-э-ю-я ä-ö-ü-ß-Ä-Ö-Ü
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
it' may seem like a small amount of space but when you have 8 char(255) columns in a table with 10 million rows you'd noticed the difference considerably. It is a small amount of space. Perhaps it was necessary in the days when 1Gb Hdds were a luxury, but those days are long gone. In the example you gave you're still only wasting approx 1 GB. Hardly a lot these days when you consider you buy a consumer 500Gb Hdd for £50. -- Richard Heyes HTML5 Graphing for FF, Chrome, Opera and Safari: http://www.rgraph.org (Updated January 4th) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
Richard Heyes wrote: So where's the advantage of VARCHAR ? Less space on disc = less data retrieved from disc = faster data retrieval - sometimes. If you have small columns, a small number of rows, or both, then char columns may be faster. If you have large columns of varying actual length, lots of rows, or both, then varchar columns may be faster. The best thing for performance is reducing the number of times you hit the disc. If you can cache (more of) a table by using varchars instead of chars for some columns, then varchars have an advantage. Knowing what your data requires leads to better results than picking in advance whether to use char or varchar (or text). Measuring is even better. Oh, and for a column with a limited range of values, enum beats 'em all! -- Ross McKay, Toronto, NSW Australia You can't control what you can't measure - Tom DeMarco -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
2009/1/7 Ross McKay ro...@zeta.org.au: Oh, and for a column with a limited range of values, enum beats 'em all! Got anything to back that up? http://www.mysqlperformanceblog.com/2008/01/24/enum-fields-vs-varchar-vs-int-joined-table-what-is-faster/ -Stuart -- http://stut.net/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
So where's the advantage of VARCHAR ? Less space on disc = less data retrieved from disc = faster data retrieval - sometimes. If you have small columns, a small number of rows, or both, then char columns may be faster. If you have large columns of varying actual length, lots of rows, or both, then varchar columns may be faster. I still think a CHAR field would be faster than a VARCHAR because of the fixed row length (assuming every thing else is fixed). Perhaps someone from the MySQL list could clarify...? -- Richard Heyes HTML5 Graphing for FF, Chrome, Opera and Safari: http://www.rgraph.org (Updated January 4th) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
Stuart wrote: Got anything to back that up? http://www.mysqlperformanceblog.com/2008/01/24/enum-fields-vs-varchar-vs-int-joined-table-what-is-faster/ Thanks, that'll do nicely. Even that strapped chicken test with tables that are likely cached shows enum as the fasted, albeit only marginally. Where disc reads are required, or even (hand-to-forehead) a large table scan, the difference would be much more significant. Not that I'd advocate enum columns widely instead of char or varchar (or text); horses for courses, whatever suits the data best. As I said in the last post, knowing what your data requires leads to better results than picking in advance whether to use char or varchar (or text). Measuring is even better. -- Ross McKay, Toronto, NSW Australia There is more to life than simply increasing its speed. - Gandhi -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
There are other factors. If a table is completely fixed in size it makes for a faster lookup time since the offset is easier to compute. This is true, at least, for myisam tables. All books on tuning that I have read have said the CHAR makes for more efficient lookup and comparison that VARCHAR. Also, I was told by the instructor at a MySQL class that all VARCHAR columns are converted to CHAR when stored in memory. Can anyone else confirm this? On Wed, Jan 7, 2009 at 7:26 AM, Richard Heyes rich...@php.net wrote: So where's the advantage of VARCHAR ? Less space on disc = less data retrieved from disc = faster data retrieval - sometimes. If you have small columns, a small number of rows, or both, then char columns may be faster. If you have large columns of varying actual length, lots of rows, or both, then varchar columns may be faster. I still think a CHAR field would be faster than a VARCHAR because of the fixed row length (assuming every thing else is fixed). Perhaps someone from the MySQL list could clarify...? -- Richard Heyes HTML5 Graphing for FF, Chrome, Opera and Safari: http://www.rgraph.org (Updated January 4th) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jlyons4...@gmail.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
On 1/7/09, Jim Lyons jlyons4...@gmail.com wrote: There are other factors. If a table is completely fixed in size it makes for a faster lookup time since the offset is easier to compute. This is true, at least, for myisam tables. All books on tuning that I have read have said the CHAR makes for more efficient lookup and comparison that VARCHAR. Also, I was told by the instructor at a MySQL class that all VARCHAR columns are converted to CHAR when stored in memory. Can anyone else confirm this? That's my recollection, also, derived from a MySQL class. IIRC, the char length is equal to the longest varchar record in the column. David
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
On Wed, Jan 7, 2009 at 9:17 AM, David Giragosian dgiragos...@gmail.com wrote: On 1/7/09, Jim Lyons jlyons4...@gmail.com wrote: There are other factors. If a table is completely fixed in size it makes for a faster lookup time since the offset is easier to compute. This is true, at least, for myisam tables. All books on tuning that I have read have said the CHAR makes for more efficient lookup and comparison that VARCHAR. Also, I was told by the instructor at a MySQL class that all VARCHAR columns are converted to CHAR when stored in memory. Can anyone else confirm this? That's my recollection, also, derived from a MySQL class. IIRC, the char length is equal to the longest varchar record in the column. Actually it's a fixed-length buffer big enough to hold the worst-case possible value, not the worst-case existing value. In bytes, no less. If it's a utf8 varchar(100), that's 300 bytes, even if the biggest value in the table is one character. -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
Richard Heyes wrote: it' may seem like a small amount of space but when you have 8 char(255) columns in a table with 10 million rows you'd noticed the difference considerably. It is a small amount of space. Perhaps it was necessary in the days when 1Gb Hdds were a luxury, but those days are long gone. In the example you gave you're still only wasting approx 1 GB. Hardly a lot these days when you consider you buy a consumer 500Gb Hdd for £50. You also have to remember that server drives are more expensive as they are of higher quality than consumer disks. They require a lot more MTBF than your normal hard drive at your local computer store. Also, if you waste 1GB in 1 column, imagine how much wasted space there is in the whole DB. Thank you, Micah Gersten onShore Networks Internal Developer http://www.onshore.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
On Wed, 2009-01-07 at 10:49 +, Richard Heyes wrote: it' may seem like a small amount of space but when you have 8 char(255) columns in a table with 10 million rows you'd noticed the difference considerably. It is a small amount of space. Perhaps it was necessary in the days when 1Gb Hdds were a luxury, but those days are long gone. In the example you gave you're still only wasting approx 1 GB. Hardly a lot these days when you consider you buy a consumer 500Gb Hdd for £50. That's for a single table. Now add another 20 to 50 tables depending on the database. If you want to throw away money go ahead, but I don't know too many clients that want to waste 10 gigs of mostly padded space. Cheers, Rob. -- http://www.interjinn.com Application and Templating Framework for PHP -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
On Wed, 2009-01-07 at 13:26 +, Richard Heyes wrote: So where's the advantage of VARCHAR ? Less space on disc = less data retrieved from disc = faster data retrieval - sometimes. If you have small columns, a small number of rows, or both, then char columns may be faster. If you have large columns of varying actual length, lots of rows, or both, then varchar columns may be faster. I still think a CHAR field would be faster than a VARCHAR because of the fixed row length (assuming every thing else is fixed). Perhaps someone from the MySQL list could clarify...? I would say it depends. For a single row request it certainly should be since the entire row can be found by pre-computing the offset. However, multiple rows would mean a larger spread accross the hard drive due to data padding and this could incurr more slow seeks (remember just because the data is contiguous doesn't mean it's stored that way physically). Cheers, Rob. -- http://www.interjinn.com Application and Templating Framework for PHP -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
That's for a single table. I've not come across many databases where 20-50 tables have 10 million rows each. And with a table of that size, then I might be coerced into thinking about the storage requirements a little more. Maybe. Now add another 20 to 50 tables depending on the database. If you want to throw away money go ahead, but I don't know too many clients that want to waste 10 gigs of mostly padded space. I don't know of many clients who care as long as it is performant and cost effective. Wasting 10 Gigs is not a great deal when you have a drive measured in the hundreds of Gigs. -- Richard Heyes HTML5 Graphing for FF, Chrome, Opera and Safari: http://www.rgraph.org (Updated January 4th) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
Richard Heyes wrote: That's for a single table. I've not come across many databases where 20-50 tables have 10 million rows each. And with a table of that size, then I might be coerced into thinking about the storage requirements a little more. Maybe. Now add another 20 to 50 tables depending on the database. If you want to throw away money go ahead, but I don't know too many clients that want to waste 10 gigs of mostly padded space. I don't know of many clients who care as long as it is performant and cost effective. Wasting 10 Gigs is not a great deal when you have a drive measured in the hundreds of Gigs. until you have to dump it, zip it, ssh it over to another box and then import it back in -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
On Wed, 2009-01-07 at 21:18 +, Nathan Rixham wrote: Richard Heyes wrote: That's for a single table. I've not come across many databases where 20-50 tables have 10 million rows each. And with a table of that size, then I might be coerced into thinking about the storage requirements a little more. Maybe. Now add another 20 to 50 tables depending on the database. If you want to throw away money go ahead, but I don't know too many clients that want to waste 10 gigs of mostly padded space. I don't know of many clients who care as long as it is performant and cost effective. Wasting 10 Gigs is not a great deal when you have a drive measured in the hundreds of Gigs. until you have to dump it, zip it, ssh it over to another box and then import it back in Not just that, but aren't there greater overheads if the database is physically larger in size? I assume that char might be a bit quicker to work with than varchar, but I am pretty certain that using a fulltext index on a text field is ridiculously slow compared to the former two. Ash www.ashleysheridan.co.uk -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
2009/1/7 Ashley Sheridan a...@ashleysheridan.co.uk: Not just that, but aren't there greater overheads if the database is physically larger in size? I assume that char might be a bit quicker to work with than varchar, but I am pretty certain that using a fulltext index on a text field is ridiculously slow compared to the former two. Especially if the programmer is not taking advantage of the database's built in search / sort and other functions. Those type of functions can run over O(n^2) if the programmer is not careful and frankly, php is a language that is used very often by novices (myself included) who may not know better. Be careful with large databases if you do not have experience with them. -- Dotan Cohen http://what-is-what.com http://gibberish.co.il א-ב-ג-ד-ה-ו-ז-ח-ט-י-ך-כ-ל-ם-מ-ן-נ-ס-ע-ף-פ-ץ-צ-ק-ר-ש-ת ا-ب-ت-ث-ج-ح-خ-د-ذ-ر-ز-س-ش-ص-ض-ط-ظ-ع-غ-ف-ق-ك-ل-م-ن-ه-و-ي А-Б-В-Г-Д-Е-Ё-Ж-З-И-Й-К-Л-М-Н-О-П-Р-С-Т-У-Ф-Х-Ц-Ч-Ш-Щ-Ъ-Ы-Ь-Э-Ю-Я а-б-в-г-д-е-ё-ж-з-и-й-к-л-м-н-о-п-р-с-т-у-ф-х-ц-ч-ш-щ-ъ-ы-ь-э-ю-я ä-ö-ü-ß-Ä-Ö-Ü
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
On Thu, 2009-01-08 at 01:07 +0200, Dotan Cohen wrote: 2009/1/7 Ashley Sheridan a...@ashleysheridan.co.uk: Not just that, but aren't there greater overheads if the database is physically larger in size? I assume that char might be a bit quicker to work with than varchar, but I am pretty certain that using a fulltext index on a text field is ridiculously slow compared to the former two. Especially if the programmer is not taking advantage of the database's built in search / sort and other functions. Those type of functions can run over O(n^2) if the programmer is not careful and frankly, php is a language that is used very often by novices (myself included) who may not know better. Be careful with large databases if you do not have experience with them. Could be worse, I see a lot of novice programming coming from the ColdFusion camp. Possibly because it has tags for most of the common needs, they don't actually need to learn how to program ;) Ash www.ashleysheridan.co.uk -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
Richard Heyes wrote: I've not come across many databases where 20-50 tables have 10 million rows each. And with a table of that size, then I might be coerced into thinking about the storage requirements a little more. Maybe. Not on MySQL, but I've worked on databases with hundreds of millions of rows, in multiple tables. Converting varchars to chars would mean huge amounts of expensive SAN storage, plus the overheads of scanning through rows on disc suddenly become important. Maybe not applicable to little website databases, but again... know your data and pick the appropriate type. I use a mix of char, varchar and text depending on the requirements. And enum :) Now add another 20 to 50 tables depending on the database. If you want to throw away money go ahead, but I don't know too many clients that want to waste 10 gigs of mostly padded space. I don't know of many clients who care as long as it is performant and cost effective. Wasting 10 Gigs is not a great deal when you have a drive measured in the hundreds of Gigs. Performant isn't a word - Microsoft marketing term. Wasting 10GB is important when it's expensive storage, backed up, replicated, transferred to other systems over comms links, etc. It also means more disc access, unless you have a surplus 10GB RAM for extra caching. Disc access is usually what kills database performance. -- Ross McKay, Toronto, NSW Australia Let the laddie play wi the knife - he'll learn - The Wee Book of Calvin -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
On Thu, 2009-01-08 at 11:37 +1100, Ross McKay wrote: Richard Heyes wrote: I've not come across many databases where 20-50 tables have 10 million rows each. And with a table of that size, then I might be coerced into thinking about the storage requirements a little more. Maybe. Not on MySQL, but I've worked on databases with hundreds of millions of rows, in multiple tables. Converting varchars to chars would mean huge amounts of expensive SAN storage, plus the overheads of scanning through rows on disc suddenly become important. Maybe not applicable to little website databases, but again... know your data and pick the appropriate type. I use a mix of char, varchar and text depending on the requirements. And enum :) Now add another 20 to 50 tables depending on the database. If you want to throw away money go ahead, but I don't know too many clients that want to waste 10 gigs of mostly padded space. I don't know of many clients who care as long as it is performant and cost effective. Wasting 10 Gigs is not a great deal when you have a drive measured in the hundreds of Gigs. Performant isn't a word - Microsoft marketing term. Wasting 10GB is important when it's expensive storage, backed up, replicated, transferred to other systems over comms links, etc. It also means more disc access, unless you have a surplus 10GB RAM for extra caching. Disc access is usually what kills database performance. -- Ross McKay, Toronto, NSW Australia Let the laddie play wi the knife - he'll learn - The Wee Book of Calvin enum is just so useful! I just haven't found the equivalent in mssql. I find the mssql offering distinctly lacking in places, especially with tools to manipulate it. mysql on the other hand is no only more powerful and friendlier, but a darn site faster too! sorry to troll, but i had a really bad week with mssql, and a very good one with mysql, and thought i'd share the love/hate thing i got going on! Ash www.ashleysheridan.co.uk -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
... It may be worth mentioning that, IIRC, CHAR is faster due to the fixed length. If you can make your table use a fixed length row size (ie no variable length columns), it'll be faster. Also I could be missing something, but I can't see the advantage in VARCHAR since space is not really a concern these days. -- Richard Heyes HTML5 Graphing for FF, Chrome, Opera and Safari: http://www.rgraph.org (Updated January 4th) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
It may be worth mentioning that, IIRC, CHAR is faster due to the fixed length. If you can make your table use a fixed length row size (ie no variable length columns), it'll be faster. I'd be interested in seeing tests about this.. I doubt there's any difference. Also I could be missing something, but I can't see the advantage in VARCHAR since space is not really a concern these days. char is fixed length and padded. If you don't fill up the space, the db does it for you (even though it seems it's internal only). http://dev.mysql.com/doc/refman/5.0/en/char.html When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
Also I could be missing something, but I can't see the advantage in VARCHAR since space is not really a concern these days. char is fixed length and padded. If you don't fill up the space, the db does it for you (even though it seems it's internal only). http://dev.mysql.com/doc/refman/5.0/en/char.html When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed. So where's the advantage of VARCHAR ? -- Richard Heyes HTML5 Graphing for FF, Chrome, Opera and Safari: http://www.rgraph.org (Updated January 4th) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
Richard Heyes wrote: Also I could be missing something, but I can't see the advantage in VARCHAR since space is not really a concern these days. char is fixed length and padded. If you don't fill up the space, the db does it for you (even though it seems it's internal only). http://dev.mysql.com/doc/refman/5.0/en/char.html When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed. So where's the advantage of VARCHAR ? storage size richard, as if you use char(100) then a string(4) will still use the space of string(100); whereas with varchar(100) it's only take up it's real space of string(4). it' may seem like a small amount of space but when you have 8 char(255) columns in a table with 10 million rows you'd noticed the difference considerably. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
chris smith wrote: It may be worth mentioning that, IIRC, CHAR is faster due to the fixed length. If you can make your table use a fixed length row size (ie no variable length columns), it'll be faster. I'd be interested in seeing tests about this.. I doubt there's any difference. quote: http://dev.mysql.com/doc/refman/5.1/en/data-size.html For MyISAM tables, if you do not have any variable-length columns (VARCHAR, TEXT, or BLOB columns), a fixed-size row format is used. *This is faster but unfortunately may waste some space.* See Section 13.4.3, “MyISAM Table Storage Formats”. You can hint that you want to have fixed length rows even if you have VARCHAR columns with the CREATE TABLE option ROW_FORMAT=FIXED. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
So what I'm taking away from this is that I can index a text column, but that is relatively new. Based on experience with technology I'm going to guess it's not a very efficient index or search function yet. CHAR seems to be well entrenched, and the favorite for any column I may need to search against. I just need to be warry of the size limits and account for them in my program and data entry. Does that seem to be the consensus? Nathan Rixham nrix...@gmail.com wrote in message news:4963388f.5080...@gmail.com... chris smith wrote: It may be worth mentioning that, IIRC, CHAR is faster due to the fixed length. If you can make your table use a fixed length row size (ie no variable length columns), it'll be faster. I'd be interested in seeing tests about this.. I doubt there's any difference. quote: http://dev.mysql.com/doc/refman/5.1/en/data-size.html For MyISAM tables, if you do not have any variable-length columns (VARCHAR, TEXT, or BLOB columns), a fixed-size row format is used. *This is faster but unfortunately may waste some space.* See Section 13.4.3, MyISAM Table Storage Formats. You can hint that you want to have fixed length rows even if you have VARCHAR columns with the CREATE TABLE option ROW_FORMAT=FIXED. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
2009/1/5 Frank Stanovcak blindspot...@comcast.net: It's been a while since I've programed (VB was on version 4) I was wondering if any one could tell me what the diff is between char, varchar, and text in mysql. I know this isn't a mysql news group, but since I am using php for the interaction it seemed like the place to ask. Thanks in advance, and have a great day! Frank http://justfuckinggoogleit.com/search?q=char+varchar+text+mysql -- Dotan Cohen http://what-is-what.com http://gibberish.co.il א-ב-ג-ד-ה-ו-ז-ח-ט-י-ך-כ-ל-ם-מ-ן-נ-ס-ע-ף-פ-ץ-צ-ק-ר-ש-ת ا-ب-ت-ث-ج-ح-خ-د-ذ-ر-ز-س-ش-ص-ض-ط-ظ-ع-غ-ف-ق-ك-ل-م-ن-ه-و-ي А-Б-В-Г-Д-Е-Ё-Ж-З-И-Й-К-Л-М-Н-О-П-Р-С-Т-У-Ф-Х-Ц-Ч-Ш-Щ-Ъ-Ы-Ь-Э-Ю-Я а-б-в-г-д-е-ё-ж-з-и-й-к-л-м-н-о-п-р-с-т-у-ф-х-ц-ч-ш-щ-ъ-ы-ь-э-ю-я ä-ö-ü-ß-Ä-Ö-Ü
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
Frank Stanovcak wrote: So what I'm taking away from this is that I can index a text column, but that is relatively new. Based on experience with technology I'm going to guess it's not a very efficient index or search function yet. CHAR seems to be well entrenched, and the favorite for any column I may need to search against. I just need to be warry of the size limits and account for them in my program and data entry. Does that seem to be the consensus? certainly not from this end; all the column types have been around since forever and it's mroe about picking the appropriate type for the job. If it's short variable length strings then use varchar. If it's short fixed or max length strings that will most frequently sit with a length near max length then use char. If it's a decent amount of text over 250 chars then use a text column. For all three you can index them using normal indexes; or if you need to use the fulltext search features then you can add a fulltext index to the column(s) in need. (only with myisam tables). You'll find the types are named v well, so if it's to hold fixed length char data use char, variable legnth char data then use varchar, bit of text use text, long bit of text use longtext and so on. :) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
On Tue, Jan 6, 2009 at 10:43 AM, Dotan Cohen dotanco...@gmail.com wrote: 2009/1/5 Frank Stanovcak blindspot...@comcast.net: It's been a while since I've programed (VB was on version 4) I was wondering if any one could tell me what the diff is between char, varchar, and text in mysql. I know this isn't a mysql news group, but since I am using php for the interaction it seemed like the place to ask. Thanks in advance, and have a great day! Frank http://justfuckinggoogleit.com/search?q=char+varchar+text+mysql -- Dotan Cohen http://what-is-what.com http://gibberish.co.il א-ב-ג-ד-ה-ו-ז-ח-ט-י-ך-כ-ל-ם-מ-ן-נ-ס-ע-ף-פ-ץ-צ-ק-ר-ש-ת ا-ب-ت-ث-ج-ح-خ-د-ذ-ر-ز-س-ش-ص-ض-ط-ظ-ع-غ-ف-ق-ك-ل-م-ن-ه-و-ي А-Б-В-Г-Д-Е-Ё-Ж-З-И-Й-К-Л-М-Н-О-П-Р-С-Т-У-Ф-Х-Ц-Ч-Ш-Щ-Ъ-Ы-Ь-Э-Ю-Я а-б-в-г-д-е-ё-ж-з-и-й-к-л-м-н-о-п-р-с-т-у-ф-х-ц-ч-ш-щ-ъ-ы-ь-э-ю-я ä-ö-ü-ß-Ä-Ö-Ü Nice :D
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
Nathan Rixham wrote: Richard Heyes wrote: Also I could be missing something, but I can't see the advantage in VARCHAR since space is not really a concern these days. char is fixed length and padded. If you don't fill up the space, the db does it for you (even though it seems it's internal only). http://dev.mysql.com/doc/refman/5.0/en/char.html When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed. So where's the advantage of VARCHAR ? storage size richard, as if you use char(100) then a string(4) will still use the space of string(100); whereas with varchar(100) it's only take up it's real space of string(4). it' may seem like a small amount of space but when you have 8 char(255) columns in a table with 10 million rows you'd noticed the difference considerably. Actually string(4) in a varchar(100) will take up 5 bytes, but that's still better than 100. Thank you, Micah Gersten onShore Networks Internal Developer http://www.onshore.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
Frank Stanovcak wrote: I know! This ranks up there with redirecting people to www.ratemypoo.com when an intrusion attempt is detected as part of the security measures. ahh i redirect them to the endless sixapart atom stream seing as it's normally spiders/bots - slows them down a bit :p -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
Nathan Rixham wrote: chris smith wrote: It may be worth mentioning that, IIRC, CHAR is faster due to the fixed length. If you can make your table use a fixed length row size (ie no variable length columns), it'll be faster. I'd be interested in seeing tests about this.. I doubt there's any difference. quote: http://dev.mysql.com/doc/refman/5.1/en/data-size.html For MyISAM tables, if you do not have any variable-length columns (VARCHAR, TEXT, or BLOB columns), a fixed-size row format is used. *This is faster but unfortunately may waste some space.* See Section 13.4.3, “MyISAM Table Storage Formats”. You can hint that you want to have fixed length rows even if you have VARCHAR columns with the CREATE TABLE option ROW_FORMAT=FIXED. It'd still be interesting to see if it made any noticeable difference (I'm guessing not until you get into rather large db's). -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
On Wed, Jan 07, 2009 at 08:38:03AM +1100, Chris wrote: Nathan Rixham wrote: chris smith wrote: It may be worth mentioning that, IIRC, CHAR is faster due to the fixed length. If you can make your table use a fixed length row size (ie no variable length columns), it'll be faster. I'd be interested in seeing tests about this.. I doubt there's any difference. quote: http://dev.mysql.com/doc/refman/5.1/en/data-size.html For MyISAM tables, if you do not have any variable-length columns (VARCHAR, TEXT, or BLOB columns), a fixed-size row format is used. *This is faster but unfortunately may waste some space.* See Section 13.4.3, ?MyISAM Table Storage Formats?. You can hint that you want to have fixed length rows even if you have VARCHAR columns with the CREATE TABLE option ROW_FORMAT=FIXED. It'd still be interesting to see if it made any noticeable difference (I'm guessing not until you get into rather large db's). FWIW, this is *not* the case with PostgreSQL, according to this note from the PostgreSQL website: Tip: There are no performance differences between these three types, apart from the increased storage size when using the blank-padded type. While character(n) has performance advantages in some other database systems, it has no such advantages in PostgreSQL. In most situations text or character varying should be used instead. http://www.postgresql.org/docs/8.0/interactive/datatype-character.html Paul -- Paul M. Foster -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
2009/1/5 Frank Stanovcak blindspot...@comcast.net: It's been a while since I've programed (VB was on version 4) I was wondering if any one could tell me what the diff is between char, varchar, and text in mysql. I know this isn't a mysql news group, but since I am using php for the interaction it seemed like the place to ask. Thanks in advance, and have a great day! char: the space required for the length of the field is allocated for each row no matter how much of it is used. varchar: only the space required for the content of the field is allocated per row but these fields are limited to 255 chars (IIRC) in length. text: for all intents and purposes these have unlimited length (4GBish IIRC). There is a page in the MySQL manual that explains all of the data formats. Google for mysql data formats and you'll likely get it. -Stuart -- http://stut.net/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
Thank you. I have a stupid/nasty habit of realizing I can search after I post a question. Problem was I was overwhelmed on google. I was hoping for a simple this type is good for this use because, but bad for other things because. type answer. My mantra around this office is I'm old not addled damnit! :) Stuart stut...@gmail.com wrote in message news:a5f019de0901051115ree20159tbbcf5b3cb2633...@mail.gmail.com... 2009/1/5 Frank Stanovcak blindspot...@comcast.net: It's been a while since I've programed (VB was on version 4) I was wondering if any one could tell me what the diff is between char, varchar, and text in mysql. I know this isn't a mysql news group, but since I am using php for the interaction it seemed like the place to ask. Thanks in advance, and have a great day! char: the space required for the length of the field is allocated for each row no matter how much of it is used. varchar: only the space required for the content of the field is allocated per row but these fields are limited to 255 chars (IIRC) in length. text: for all intents and purposes these have unlimited length (4GBish IIRC). There is a page in the MySQL manual that explains all of the data formats. Google for mysql data formats and you'll likely get it. -Stuart -- http://stut.net/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
On Mon, Jan 5, 2009 at 14:05, Frank Stanovcak blindspot...@comcast.net wrote: It's been a while since I've programed (VB was on version 4) I was wondering if any one could tell me what the diff is between char, varchar, and text in mysql. I know this isn't a mysql news group, but since I am using php for the interaction it seemed like the place to ask. Thanks in advance, and have a great day! This doesn't explain it all, of course, but the most basic explanation is: CHAR(n)Uses n bytes regardless of length of data inserted[^1] VARCHAR(n) Uses strlen+1 bytes[^2] TEXT Uses the highest amount of minimum bytes, even for empty[^3] 1: Quick SELECT/INSERT/UPDATE; requires (n) length; maxlength is 255; wastes space. 2: Slow SELECT/INSERT/UPDATE; requires (n) length; maxlength is 65535; doesn't waste space.[^4] 3: Moderate SELECT/INSERT/UPDATE; doesn't require (n); maxlength is 65535; wastes space.[^5][^6] 4: VARCHAR is further limited to the maximum row size of 65535. 5: TEXT maxlength per column is 65535, but can have multiple =65535 columns on same row. 6: TEXT includes four subcategories: TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. -- /Daniel P. Brown daniel.br...@parasane.net || danbr...@php.net http://www.parasane.net/ || http://www.pilotpig.net/ Unadvertised dedicated server deals, too low to print - email me to find out! -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
Frank Stanovcak wrote: It's been a while since I've programed (VB was on version 4) I was wondering if any one could tell me what the diff is between char, varchar, and text in mysql. I know this isn't a mysql news group, but since I am using php for the interaction it seemed like the place to ask. Thanks in advance, and have a great day! Frank As nice as the guys on the list are, this will be most accurate: http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html Thank you, Micah Gersten onShore Networks Internal Developer http://www.onshore.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
On Mon, 2009-01-05 at 19:15 +, Stuart wrote: 2009/1/5 Frank Stanovcak blindspot...@comcast.net: It's been a while since I've programed (VB was on version 4) I was wondering if any one could tell me what the diff is between char, varchar, and text in mysql. I know this isn't a mysql news group, but since I am using php for the interaction it seemed like the place to ask. Thanks in advance, and have a great day! char: the space required for the length of the field is allocated for each row no matter how much of it is used. varchar: only the space required for the content of the field is allocated per row but these fields are limited to 255 chars (IIRC) in length. text: for all intents and purposes these have unlimited length (4GBish IIRC). There is a page in the MySQL manual that explains all of the data formats. Google for mysql data formats and you'll likely get it. It's generally worth mentioning that you can usually index char or varchar, but not text. Cheers, Rob. -- http://www.interjinn.com Application and Templating Framework for PHP -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
On Mon, Jan 5, 2009 at 14:54, Robert Cummings rob...@interjinn.com wrote: It's generally worth mentioning that you can usually index char or varchar, but not text. Actually, you can with MyISAM tables using FULLTEXT. -- /Daniel P. Brown daniel.br...@parasane.net || danbr...@php.net http://www.parasane.net/ || http://www.pilotpig.net/ Unadvertised dedicated server deals, too low to print - email me to find out! -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
First, start here: http://dev.mysql.com/doc/refman/5.1/en/string-types.html Stuart wrote: varchar: only the space required for the content of the field is allocated per row but these fields are limited to 255 chars (IIRC) in length. In MySQL, varchar can hold up to 65,535 characters, but the actual maximum size is limited by the maximum row length (65,535 bytes) and the character set (e.g. utf8 uses between one and three bytes per character). Maybe you're thinking of char, which is limited to 255 characters. Robert Cummings wrote: It's generally worth mentioning that you can usually index char or varchar, but not text. No, you can index a text column, but it will only index part of the column: Only the first max_sort_length bytes of the column are used when sorting. The default value of max_sort_length is 1024. - TFM. Also, you can create a FULLTEXT index on text columns stored in the MyISAM engine. (which is a PITA, because if you want ACID transactions and full-text searching, you need to create and maintain a MyISAM shadow table of the data you want to full-text search on) -- Ross McKay, Toronto, NSW Australia The lawn could stand another mowing; funny, I don't even care - Elvis Costello -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
Ross McKay wrote: First, start here: http://dev.mysql.com/doc/refman/5.1/en/string-types.html Stuart wrote: varchar: only the space required for the content of the field is allocated per row but these fields are limited to 255 chars (IIRC) in length. In MySQL, varchar can hold up to 65,535 characters, but the actual maximum size is limited by the maximum row length (65,535 bytes) and the character set (e.g. utf8 uses between one and three bytes per character). Maybe you're thinking of char, which is limited to 255 characters. You're referencing the 5.1 manual. In the 5.0 manual it says that VARCHAR was extended to 65535 in 5.0.3, so you're statement is not entirely correct, nor was Stuart's. That's why I linked him to the 5.0 manual page on data types. Thank you, Micah Gersten onShore Networks Internal Developer http://www.onshore.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
Micah Gersten wrote: You're referencing the 5.1 manual. In the 5.0 manual it says that VARCHAR was extended to 65535 in 5.0.3, so you're statement is not entirely correct, nor was Stuart's. That's why I linked him to the 5.0 manual page on data types. Good point, thanks! I usually read from the .chm manual but just jumped to the website to grab a link. Should have been: http://dev.mysql.com/doc/refman/5.0/en/string-types.html I note that Stuart was most likely talking about MySQL = 4 which had a limit of 255 characters for varchar. -- Ross McKay, Toronto, NSW Australia My old man told me one time, you never get wise, you only get older - Dandy Warhols -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
On Tue, 2009-01-06 at 11:01 +1100, Ross McKay wrote: Micah Gersten wrote: You're referencing the 5.1 manual. In the 5.0 manual it says that VARCHAR was extended to 65535 in 5.0.3, so you're statement is not entirely correct, nor was Stuart's. That's why I linked him to the 5.0 manual page on data types. Good point, thanks! I usually read from the .chm manual but just jumped to the website to grab a link. Should have been: http://dev.mysql.com/doc/refman/5.0/en/string-types.html I note that Stuart was most likely talking about MySQL = 4 which had a limit of 255 characters for varchar. I guess it's time for me to re-read the complete up-to-date manual too... You can have indexes on BLOB and TEXT columns only as of MySQL 3.23.2 for MyISAM tables or MySQL 4.0.14 for InnoDB tables. Previous versions of MySQL did not support indexing these data types. :) Cheers, Rob. -- http://www.interjinn.com Application and Templating Framework for PHP -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
