Re: Best field type for exact matches on alphanumeric fields.
We were storing relational data all in one field in XML. We now have a need to search through that data and we're now doing fulltext searches. And as you can imagine, this is getting painfully slow. Indeed NDB does not support fulltext search. So if TEXT fieldtypes are stored in a separate table, and Varchar fields are max length. What am I to do? Why are TINYTEXT fields not useful? In Cluster, TINYTEXT is just an expensive way to store 8+256 bytes. If I setup the fieldtypes with the limiter 'varchar(6)' it'll still store the maxlength? That would use 8 bytes always (1+6 round up to 4 bytes). If you specify say Varchar(255) then each tuple uses 1+255 bytes, even if you only store 'abc123' which should take 8 bytes. This is so in 5.0. -- Pekka Nousiainen, Software Engineer MySQL AB, www.mysql.com [EMAIL PROTECTED] +46 (0) 73 068 4978 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Best field type for exact matches on alphanumeric fields.
I'm runnung MySQL-Cluster 5.0.15 on Linux. I have to do some re-design of a website database that has quickly outgrown itself. I'm trying to migrate to MySQL Cluster 5.0.15. Mostly I've used VarChar fieldtypes in the past, but I want to make sure I'm using the best fieldtype for alphanumeric indexed fields. Some of the data I'm storing will be fixed in length and some will be variable in length. I've thought of just going to TINYTEXT and TEXT fields. I'm really wanting good memory utilization and speed. Perhaps there's a way to do this and get a reasonable combo of both. Are there any tutorials for setting up MySQL Field Types (for a somewhat-newbie to advanced database design) on the net? Cory @ SkyVantage -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Best field type for exact matches on alphanumeric fields.
I'm runnung MySQL-Cluster 5.0.15 on Linux. I have to do some re-design of a website database that has quickly outgrown itself. I'm trying to migrate to MySQL Cluster 5.0.15. Mostly I've used VarChar fieldtypes in the past, but I want to make sure I'm using the best fieldtype for alphanumeric indexed fields. Some of the data I'm storing will be fixed in length and some will be variable in length. I've thought of just going to TINYTEXT and TEXT fields. I'm really wanting good memory utilization and speed. Perhaps there's a way to do this and get a reasonable combo of both. Are there any tutorials for setting up MySQL Field Types (for a somewhat-newbie to advanced database design) on the net? Cory @ SkyVantage -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Best field type for exact matches on alphanumeric fields.
I'm runnung MySQL-Cluster 5.0.15 on Linux. I have to do some re-design of a website database that has quickly outgrown itself. I'm trying to migrate to MySQL Cluster 5.0.15. Mostly I've used VarChar fieldtypes in the past, but I want to make sure I'm using the best fieldtype for alphanumeric indexed fields. Some of the data I'm storing will be fixed in length and some will be variable in length. I've thought of just going to TINYTEXT and TEXT fields. I'm really wanting good memory utilization and speed. Perhaps there's a way to do this and get a reasonable combo of both. Are there any tutorials for setting up MySQL Field Types (for a somewhat-newbie to advanced database design) on the net? Cory @ SkyVantage -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best field type for exact matches on alphanumeric fields.
I have to do some re-design of a website database that has quickly outgrown itself. I'm trying to migrate to MySQL Cluster 5.0.15. Outgrown size or performance? Cluster 5.0 is memory based, databases are limited to a few gigs. Throughput can be good if application is _massively_ parallel. But the unique feature of Cluster is redundancy. Mostly I've used VarChar fieldtypes in the past, but I want to make sure I'm using the best fieldtype for alphanumeric indexed fields. In Cluster 5.0 Varchar is stored with maximum length. This changes in 5.0. For indexing, non-binary character sets have high overhead. Some of the data I'm storing will be fixed in length and some will be variable in length. I've thought of just going to TINYTEXT and TEXT fields. TINYTEXT is not useful, just use Varchar. TEXT is a form of BLOB and the bulk of it (in Cluster) is stored in a separate table. This is usually not efficient. I hope this helps to decide if Cluster is right solution for you. -- Pekka Nousiainen, Software Engineer MySQL AB, www.mysql.com [EMAIL PROTECTED] +46 (0) 73 068 4978 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best field type for exact matches on alphanumeric fields.
We were storing relational data all in one field in XML. We now have a need to search through that data and we're now doing fulltext searches. And as you can imagine, this is getting painfully slow. We're now designing a true normalized database (well, normalized within reason) with table relations rather than storing the XML. We're using cluster because we need a very fast and highly available setup. We're beyond that decision. So if TEXT fieldtypes are stored in a separate table, and Varchar fields are max length. What am I to do? Why are TINYTEXT fields not useful? If I setup the fieldtypes with the limiter 'varchar(6)' it'll still store the maxlength? [EMAIL PROTECTED] wrote: I have to do some re-design of a website database that has quickly outgrown itself. I'm trying to migrate to MySQL Cluster 5.0.15. Outgrown size or performance? Cluster 5.0 is memory based, databases are limited to a few gigs. Throughput can be good if application is _massively_ parallel. But the unique feature of Cluster is redundancy. Mostly I've used VarChar fieldtypes in the past, but I want to make sure I'm using the best fieldtype for alphanumeric indexed fields. In Cluster 5.0 Varchar is stored with maximum length. This changes in 5.0. For indexing, non-binary character sets have high overhead. Some of the data I'm storing will be fixed in length and some will be variable in length. I've thought of just going to TINYTEXT and TEXT fields. TINYTEXT is not useful, just use Varchar. TEXT is a form of BLOB and the bulk of it (in Cluster) is stored in a separate table. This is usually not efficient. I hope this helps to decide if Cluster is right solution for you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best field type for exact matches on alphanumeric fields.
Hello. Have a look here: http://dev.mysql.com/doc/refman/5.0/en/data-size.html You can freely download a sample character about data types from perfect book (MySQL 3-d edition): http://www.samspublishing.com/articles/article.asp?p=374690 You said that you're going to TEXT fields, may be you should be aware of this quote from: http://dev.mysql.com/doc/refman/5.0/en/mysql-cluster-limitations.html Text indexes are not supported. Cory Robin wrote: I'm runnung MySQL-Cluster 5.0.15 on Linux. I have to do some re-design of a website database that has quickly outgrown itself. I'm trying to migrate to MySQL Cluster 5.0.15. Mostly I've used VarChar fieldtypes in the past, but I want to make sure I'm using the best fieldtype for alphanumeric indexed fields. Some of the data I'm storing will be fixed in length and some will be variable in length. I've thought of just going to TINYTEXT and TEXT fields. I'm really wanting good memory utilization and speed. Perhaps there's a way to do this and get a reasonable combo of both. Are there any tutorials for setting up MySQL Field Types (for a somewhat-newbie to advanced database design) on the net? Cory @ SkyVantage -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
