Performance difference between "varcharColumn IS NOT NULL" and "tinyintColumn = 1"?

Is there a big performance difference between "varcharColumn IS NOT NULL" and "tinyint = 1"? Both columns a开发者_如何学Pythonre indexed.

I'm wondering if I should have:

TINYINT errorFlag
VARCHAR(255) errorMessage

or if I can do with just

VARCHAR(255) errorMessage

A column with an index on will usuall be quicker than one without, but otherwise it shouldn't make a noticable difference.

In other words, it will depend entirely what query you are doing, what other columns there are in the table, and in your query, and on what indexes you have.

For example, if there is no index on either errorFlag or errorMessage they will both be slow.

On the other hand, Unless you are writing a massively high-throughput system, or one with millions of rows, it won't really matter which you pick.

The errorFlag column is 100% duplication. Stick with errorMessage.

Or, depending on the query patterns and data distribution, you may also consider creating an additional table like below:

table your_original_table(
   original_id
  ,lots
  ,of
  ,other
  ,columns
  ,primary key(original_id)
)

table errors(
   original_id
  ,errormessage
  ,primary key(original_id)
  ,foreign key(original_id) references your_original_table(original_id)
)

The above approach provides a fast way of fetching all the errors, since each and every row in the errors is an error. Also, it makes your original table smaller and thus faster. Use this approach when most of your queries doesn't reference the errorMessage column, and when they do, they are specifically interested in the errors.

However, the above approach is NOT a good idea at all, if all or most of your queries filter the non-errors, i.e. something like where errorMessage is null. Because that would require you to always do an anti join with the errors table.