Importance of MySQL field types

Question

In a MySQL table, I have several columns - some containing strings, some dates or numbers. I have always just made each field varchar(200). What are the benefits of creating more appropriate field types? Will select queries become faster?

Answer 1

Do I understand you correctly, you store numbers and dates in varchar(200) columns?

Well, you can't compare dates, make calculations with the numbers etc. that way.

Consider a query like the following:

SELECT    yourfieldlist
FROM      YourTable
WHERE     DateColumn > '2009-10-01'

This won't work as expected if DateColumn is a varchar(200). So, it is important to use appropriate types for columns.

If you just use this one datatype, why even bother using a database? Just stuff your data in a textfile.

Answer 2

Using datatypes means that you'll be able to actually use the data in a meaningful way. For example, you can do date arithmetic (using DATE_ADD, for example).

It also means that ordering by numbers will work as expected (it won't with strings).

It also allows mysql to choose the appropriate amount/type of storage for the data you're representing.

There are many reasons, not least that it makes it easier for anyone else using your schema to know what kinds of values to expect. It also allows mysql to do validation on your data (what happens if your "date" column contains "Once in a blue moon"?)

Answer 3

The type tells the DBMS how to compare against that field. This allows you to, for example, find rows where date_created > 'some date' easily.

In short, yes, your selects will be faster if you use the proper types and are comparing against those fields because MySQL will know how to better index your data to return results more efficiently. If you are just doing basic selects without any kind of join, where or order then you probably wont see speed improvements.

Answer 4

Short answer: yes, they will most likely be faster.

If you have lots of rows, you'll save lots of disk space. For instance, an INT uses less space than VARCHAR(200). Also, choosing relevant column types will give you a certain measure of type safety (depending on how much typecasting your code or database will do for you).

Answer 5

10x-100x speed when working with indexes in numeric fields

Answer 6

Another benefit is that datatypes ensure the correctness of the data provided. Usually, anyways --- MySQL is not held in high regard for its correctness-ensurance. For example, "laksjfd" is not a valid date. Nor is "2009-02-29" --- but "2012-02-29" is. Those are all valid varchar(200)s, though.