Purpose of MySQL data type

Question

Often when creating a new web app and configuring MySQL, certain fields will need to change in format as the app develops. For instance, I may change the format of a date field or a field which once was just int now needs a letter or what not. So usually I just make every field Varchar 255 until Im finished at which time Ill change data types to correct ones.

So my question is, how important are data types? Whats the purpose? Speed?

Answer 1

Short answer: Speed, optimal data storage, Index, Search ability.

Your database can better make use of the internal works when everything has the correct datatype. You can also make better use of the special database function.

If you want to retrieve all data from a certain month. You can use special database functions to just group by a month when using a date time datatype for instance. When using a varchar to store a date, you lose all of that functionality.

Most databases are able to search or retrieve data faster from a table when the index has been created on a INT datatype. So if you had created that storage field as a varchar you would have lost performance

Answer 2

Data types are extremely important - without them, there's no way to be sure what data is actually stored in the column. For example, a VARCHAR(255) could be anything - string, numeric, date/time... It makes things extremely difficult to find particular data if you can't guarantee what the data is or any form on consistency.

Which means data typing also provides basic validation - can't stick letters into a column with a numeric or date/time data type...

The speed of data retrieval is also improved by using indexes on a column, but an index can not be used if you have to change the data type of the data to something else before the data can be compared.

It's a good thing to realize what the difference is between data, and presentation. Like the case with date field formatting - that's presentation, which can be easily taken care of if the data type is DATETIME (or converted to DATETIME).

Answer 3

Performance is one reason but so far no ones talked about running these types of queries (which wouldn't be possible if all types are VARCHAR)

SELECT SUM(total) total FROM purchases WHERE status = 'PAID';
SELECT * FROM users WHERE DATE_ADD(last_login, INTERVAL 3 MONTH) < NOW();