As mentioned in comments (it is really answer for original question) - using function in WHERE - prevent any indexes using.
First of all, You can check - how many of Your queries use form
SELECT * FROM
this is really bad practice with applications, exactly because - if You add new column, some of Your code could be broken. For example:
INSERT into table2 SELECT * FROM table1
If You can fix this (or You not have problems code), You can:
- convert IncorporationDate to Date format and use this column
- or easy add 1 more column with proper Date type and use it in queries
But first - You must be sure, all other code will not be broken by new column.
Then You can use one of the tools from answer for Your other question:
now much more similar tools present on market, many of them free in parts cover 99% of life cases.
And ETL it more often solution rather than work with build functions only.
For answer about next potential question - what with speed?
we load tables from 4M to 40M between instances - MySQL, MS SQL, Redshift
For "small" we use direct insert from ETL tools, for Big - prepare clean file for load, than direct load (Redshift from S3)
Just add live example - "how it could work"
One of Our scripts - run series of queries with search target records by substring from 2 columns + other parameters.
Changing the structure also not possible by the same reasons - a lot of place where need to make changes and no free developers resources. 2 table 3M rows per month.
- Original time - 7.5 Hours
- Modified process - prepare with Talend tables for past month with all optimised structure.
- modification only this script - for exclude substrings from SELECT query, do not touch any other in system. SELECT from prepared tables, write result to same as in original.
Prepare - 5m, script execution - 12m, total time of whole process - 17minutes.