Menu
And if so, why? I mean, is a tinyint faster to search than int?
If so, what are the practical differences in performance?
511
As a typical company's amount of data has grown exponentially it's become even more critical to optimize data storage. The size of your data doesn't just impact storage size and costs, it also affects query performance. A key factor in determining the size of your data is the data type you select.
There are five factors that influence database performance: workload, throughput, resources, optimization, and contention. The workload that is requested of the DBMS defines the demand.
The data type lets the database know what to expect from each column and also determines the kind of interactions that can occur. For example, if you want a column to contain only integers, you can use the “int” data type for it.
Most importantly, you lose proper validation – with a string, anybody can put "12/42/9999" or "36/8/211" or "foo" into that column. Depending on what format you store and how you run conversions, you will lose the ability to perform range queries, and you will have definite losses in the area of index operations.
Depending on the data types, yes, it does make a difference.
int vs. tinyint wouldn't make a noticeable difference in speed, but it would make a difference in data sizes. Assuming tinyint is 1 byte, versus int being 4, that's 3 bytes saved every row. it adds up after awhile.
Now, if it was int against varchar, then there would be a bit of a drop, as things like sorts would be much faster on integer values than string values.
If it's a comparable type, and you're not very pressed for space, go with the one that's easier and more robust.
88
Theoretically, yes, a tinyint is faster than an int. But good database design and proper indexing has a far more substantial effect on performance, so I always use int for design simplicity.
41
I would venture that there are no practical performance differences in that case. Storage space is the more substantial factor, but even then, it's not much difference. The difference is perhaps 2 bytes? After 500,000 rows you've almost used an extra megabyte. Hopefully you aren't pinching megabytes if you are working with that much data.
22
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
