SQL: best way to store yes/no values? Looking after performance in huge databases

I have some columns where I have to store basically yes/no values. For example user status for active or inactive. Newsletter suscription status for suscribed or unsuscribed.

Well I want to know (considering tables with a lot of records) if the best way is to put a tiny int with char length of 1 and set 1 for yes, and 0 for no.

Is this a correct thought? Or there are no impact in the performance of db queries when using just words like yes, no, active, inactive, suscribed, etc.

thanks in advance.

Answers


Semantically, I suggest you use bit if it's available to you. When looking at the column, any other developer can immediately determine that a boolean value is stored in it. If you don't have bit, try using tinyint. Ensuring that 1 is the only true value and 0 is the only false value will bring consistency. Otherwise, you could end up with a messy mixture of true/false, yes/no, valid/invalid, y/n, and/or t/f.

Comparing bit or tinyint values probably isn't slower than comparing strings, and even if it were slower than comparing strings, I can't imagine it having a significant effect on overall speed.


Need Your Help

Insert into on duplicate key Composite Key

mysql sql

I want to keep my m:n table in sync with another mysql database table of another system.

Searching with thinking_sphinx and filtering results

ruby-on-rails scope thinking-sphinx

I have this scenario where I thought it would be pretty basic, but found out that I can't really achieve what I need. This is why I have this question for a thinking_sphinx's expert.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.