What's the best field type and data usage to accelerate searches?

Question

I have a question about equivalent search result, that can occur on different fields.

Let's say I record the logical deletion state of records with 3 fields like :

• Boolean Deleted
• Date DeleteDate
• String DeleteUserName

The goal of the query is to avoid having deleted records into my selection. So I can search the first field with only 2 values (True/False), or on the deletion date : a lot of different values, or on the username, different values but not a lot.

What will give the best search result time, regarding the index usage ?

Answer 1

What queries do you intend to exeute? Something like this:

SELECT * FROM mytable WHERE `deleted`=0

or

SELECT * FROM mytable WHERE `deleted_date` IS NULL

They should be about the same if deleted and deleted_date are indexed.

Answer 2

If you're doing:

SELECT * FROM table WHERE 'deleted' = 0

(In that format) then it would be the fastest: The others don't search faster or slower, however, there is less overhead required to compare Bools than to compare Strings/Dates. (Usually, aspects of different systems change, some Bools are stored as 8-bit, and not optimized per equality testing)