can date datatype varchar use index as date datatype datetime index?

Question

I have an existing table that have a column date but varchar datatype and also a date datetime datatype

example:

tbl_name: trx1

columns: trx_id, activity, name, date(datetime)

index: date_of_birth

---

tbl_name: trx2

columns: trx_id, activity, name, date(varchar)

index: date_of_birth

is it ok to index date as varchar on trx2 and get the same expectation as trx1? OR

The best thing to do is to change the datatype to datetime also

Any comment or suggestion would be a great help TIA!

• Akina: 1) Use not verbal structures description but complete CREATE TABLE scripts. 2) What's the goal of such indexing? What operation/query should be improved? PS. If you store the date value than you'd use DATE datatype, not VARCHAR.
• Nullified: we're trying to improve multiple UNION ALL using date thanks
• Akina: If you use UNION ALL over DATETIME and VARCHAR datatypes combined in one output column then all DATETIME values are converted to VARCHAR which needs in additional resources. So altering the datatype is reasonable.

Answer 1

The best thing to do is to change the datatype to datetime also

Short Answer - Yes!
Always store data values in fields of the correct Data Type.
It simply avoids problems just like this and makes the full range of Data Type-specific functions available to you. For example, you can't do Date "arithmetic" on varchar fields (not without the inefficient and slow implicit type conversions and, probably, the Table Scanning that goes with it).

Also, why do you have two (or more) tables with the same structure?
The "Table-per-Thing" Model almost always breaks down. A single table, with an extra column to distinguish between populations and proper indexing can serve you just as well, if not better.