Mysql and the date function, does it process it on each row?

I have a query that does a search by date. Here is the WHERE clause:

WHERE DATE_FORMAT(listing.trackdate,'%Y-%m-%d') >= STR_TO_DATE('$search_sdate','%m/%d/%Y')

Does MySQL have to process the date_format and str_to_date for each row to fulfill the where clause? Or is there a more optimized way of doing that? The field in the table is a datetime type. Its MySQL version 5.x. $search_sdate is a date received from a web form.

Edit: To clarify I want to compare part of the date field. T开发者_JAVA百科he date field type is a datetime that stores the date plus the time of when each row was inserted. But for this particular where clause I just want to compare the date portion of the field. Hopefully that clarifies it better, sorry for the confusion.

If listing.trackdate is a DATE field, why does it need formatting to be compared with another date? It appears you're taking a date value and turning it into a string so that you can compare it with a string value that you've turned into a date.

The redundancy in that approach should, I hope, be apparent.

As to the first question you ask, of course DATE_FORMAT(listing.trackdate) must be evaluated for every row, as the value in that field will change. But it seems that

WHERE listing.trackdate >= STR_TO_DATE('$search_sdate','%m/%d/%Y')

should suffice, if trackdate is, indeed, a DATE field.

You're asking the right question. To generalize it a bit, you should avoid SQL statements that include operations of any type on a table field of any type. Not only does it add processing for every record being considered, but it also prevents the optimizer from including any indexes including that field.

If you want an answer involving a range of times (say a date), use "WHERE datetimefield BETWEEN (midnight and midnight)" - or more precisely, "WHERE datetimefield >= midnight and datetimefield < nextmidnight".

Aren't you able to perform the WHERE clause without the formatting functions? MySQL shouldn't need to format it properly in order to process a comparison. Your formatting should only be done in SELECT