Q:

Should you index on week number?

I have several large MySQL transactional tables that include a transaction date stored in datetime format. 
The tables are often queried using the YEARWEEK() function. For example:
SELECT * WHERE YEARWEEK(workdate, 3) = '201402'

There is already an index on the datetime field (i.e. 'workdate'). Should an additional index be added to optimize the performance of queries like the sample above?

A:

You don't need an additional index.  Well, one hopes not.  MySQL does not support functional indexes, except in the very limited case of indexing prefixes for strings.  So, move the functions to the constants:
WHERE workdate between STR_TO_DATE('201402 Monday', '%x%v %W') and
                       STR_TO_DATE('201402 Sunday', '%x%v %W')

An existing index on workdate should be used for this query.

