MY PLATFORM:
PHP & mySQL
MY SITUATION:
I came across a situation where I need to store a value for user selection in one of my columns of a table. Now my options would be to:
MY QUESTIONS:
So I wanted to know, which of the above two types:
Leads to faster query speed when that column is accessed (for the sake of simplicity, let's leave out mixing other queries or accessing other columns, please).
Is the most efficient way of storing and accessing data and why?
How does the access speed vary if the columns are indexed and when they are not?
My understanding is that since char(1) and tinyint(1) take up only 1 byte space, storage space will not be an issue in this case. Then what would remain is the access speed. As far as I know, numeric indexing is faster and more efficient than anything else. But the case here is tough one to decide, I think. Would definitely like to hear your experience on this one.
Thank you in advance.
The TINYINT takes 1 byte that means it has range -128 to +127 while int takes 4 bytes; it has range -2147483648 to +2147483647.
TINYTEXT can store up to 255 characters i.e 255 bytes.
MySQL a-z in Telugu Yes, MySQL internally convert bool to tinyint(1) because tinyint is the smallest integer data type. Look at the above sample output, the column isAgeGreaterThan18 data type is converted from bool to tinyint(1) internally.
Rate insert tinyint(1) insert char(1) insert enum('y', 'n') insert tinyint(1) 207/s -- -1% -20% insert char(1) 210/s 1% -- -19% insert enum('y', 'n') 259/s 25% 23% -- Rate insert char(1) insert tinyint(1) insert enum('y', 'n') insert char(1) 221/s -- -1% -13% insert tinyint(1) 222/s 1% -- -13% insert enum('y', 'n') 254/s 15% 14% -- Rate insert tinyint(1) insert char(1) insert enum('y', 'n') insert tinyint(1) 234/s -- -3% -5% insert char(1) 242/s 3% -- -2% insert enum('y', 'n') 248/s 6% 2% -- Rate insert enum('y', 'n') insert tinyint(1) insert char(1) insert enum('y', 'n') 189/s -- -6% -19% insert tinyint(1) 201/s 7% -- -14% insert char(1) 234/s 24% 16% -- Rate insert char(1) insert enum('y', 'n') insert tinyint(1) insert char(1) 204/s -- -4% -8% insert enum('y', 'n') 213/s 4% -- -4% insert tinyint(1) 222/s 9% 4% --
it seems that, for the most part, enum('y', 'n')
is faster to insert into.
Rate select char(1) select tinyint(1) select enum('y', 'n') select char(1) 188/s -- -7% -8% select tinyint(1) 203/s 8% -- -1% select enum('y', 'n') 204/s 9% 1% -- Rate select char(1) select tinyint(1) select enum('y', 'n') select char(1) 178/s -- -25% -27% select tinyint(1) 236/s 33% -- -3% select enum('y', 'n') 244/s 37% 3% -- Rate select char(1) select tinyint(1) select enum('y', 'n') select char(1) 183/s -- -16% -21% select tinyint(1) 219/s 20% -- -6% select enum('y', 'n') 233/s 27% 6% -- Rate select tinyint(1) select char(1) select enum('y', 'n') select tinyint(1) 217/s -- -1% -4% select char(1) 221/s 1% -- -2% select enum('y', 'n') 226/s 4% 2% -- Rate select char(1) select tinyint(1) select enum('y', 'n') select char(1) 179/s -- -14% -20% select tinyint(1) 208/s 17% -- -7% select enum('y', 'n') 224/s 25% 7% --
Selecting also seems to be the enum
. Code can be found here
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