MYSQL DATE function running insanely slow in LEFT JOIN

Question

When adding the line:

LEFT JOIN core_records_sales as sales ON DATE(appointments.date) = DATE(sales.date_sold)

To my query, it boosts the time for the script to run from about 8 seconds, to 2-3 minutes.

Would there be some data causing this problem or am I not implementing the function correctly?

I need to use DATE() because I need them to be the same day but the date fields are DATETIME

Answer 1

This is almost certainly an issue with the appointments.date field being indexed. Adding the use of the DATE() function makes it so that index cannot be used, forcing a table scan.

I've had to deal with this before, and the best way I've found to solve the issue is to have a separate column with just the date part (no time) in it or to store the date and time in two separate columns.

I'd love to hear if others have a better way of dealing with that though.