How does one create an index on the date part of DATETIME field in MySql

If I remember correctly, that will run a whole table scan because you're passing the column through a function. MySQL will obediently run the function for each and every column, bypassing the index since the query optimizer can't really know the results of the function.

What I would do is something like:

SELECT * FROM transactionlist 
WHERE TranDateTime BETWEEN '2008-08-17' AND '2008-08-17 23:59:59.999999';

That should give you everything that happened on 2008-08-17.

I don't mean to sound cute, but a simple way would be to add a new column that only contained the date part and index on that.

Another option (relevant for version 5.7.3 and above) is to create a generated/virtual column based on the datetime column, then index it.

CREATE TABLE `table` (
`my_datetime` datetime NOT NULL,
`my_date` varchar(12) GENERATED ALWAYS AS (DATE(`my_datetime`)) STORED,
KEY `my_idx` (`my_date`)
) ENGINE=InnoDB;

You can't create an index on just the date part. Is there a reason you have to?

Even if you could create an index on just the date part, the optimiser would probably still not use it for the above query.

I think you'll find that

SELECT * FROM transactionlist WHERE TranDateTime BETWEEN '2008-08-17' AND '2008-08-18'

Is efficient and does what you want.