Most flexible data type

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Most flexible data type

Post by JellyFish »

What is the most flexible data type for MySQL? What can hold the most variety of data, while at the same time being able to hold the most amount of data?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post by Christopher »

That's a couple of questions in one. The TEXT and BLOB types can hold the most data. I would think that string types would be more flexible than numeric types because they can also hold numbers that can be converted back to numerics.
(#10850)
mrkite
Forum Contributor
Posts: 104
Joined: Tue Sep 11, 2007 4:19 am

Post by mrkite »

blobs can hold any data.

But blobs and text can't be indexed. Text can be fulltext indexed, but that's about it. I don't believe you can create any index on blobs.

Blobs aren't used much. Anywhere a blob would be appropriate, saving a file on disk and storing its filename in mysql is more appropriate.
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Post by JellyFish »

mrkite wrote:blobs can hold any data.

But blobs and text can't be indexed. Text can be fulltext indexed, but that's about it. I don't believe you can create any index on blobs.

Blobs aren't used much. Anywhere a blob would be appropriate, saving a file on disk and storing its filename in mysql is more appropriate.
What's indexed?
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

mrkite wrote:Blobs aren't used much. Anywhere a blob would be appropriate, saving a file on disk and storing its filename in mysql is more appropriate.
The result of AES_ENCRYPT() should be stored in a blob. That's extremely useful
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post by califdon »

You shouldn't really think in terms of "flexible". Certain data types are appropriate for different kinds of data, that's all. It would be bad database design to try to store different kinds of data (say, dates and telephone numbers) in the same field of a table, because the underlying concepts of relational databases expect data in any field of a table to be consistent. If it's not, you're really asking for trouble, later on. So the proper question is, what is the appropriate data type for a particular field.
User avatar
Josh1billion
Forum Contributor
Posts: 316
Joined: Tue Sep 11, 2007 3:25 pm

Post by Josh1billion »

JellyFish wrote:
mrkite wrote:blobs can hold any data.

But blobs and text can't be indexed. Text can be fulltext indexed, but that's about it. I don't believe you can create any index on blobs.

Blobs aren't used much. Anywhere a blob would be appropriate, saving a file on disk and storing its filename in mysql is more appropriate.
What's indexed?
An index is a field that has the "primary" (key) attribute. That "primary" attribute means that each record must have a unique value for that field, so this is usually used for "id" fields.
mrkite
Forum Contributor
Posts: 104
Joined: Tue Sep 11, 2007 4:19 am

Post by mrkite »

Josh1billion wrote: An index is a field that has the "primary" (key) attribute. That "primary" attribute means that each record must have a unique value for that field, so this is usually used for "id" fields.
Primary key is just one type of index. Anything that you're going to use in a where statement should be indexed.

For example, if you say "select * from table where age=32;" If age doesn't have an index, mysql has to scan the entire table looking for matches. If you have a million rows in that table, it has to look through every single row and check for a match. If you add an index to age, then mysql creates a btree and instead of a million rows, it only has to look at roughly 6 rows (depending on how well-balanced the tree is).

btw, it looks like mysql does allow you to index text and blobs, but you must specify a prefix length.. meaning that only the first X bytes of the field will be indexed.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

To answer the original question: BLOB
Post Reply