Is MySQL smart enough to not re-query for the max date for every item?

I've got a query which does a join like this:

LEFT JOIN history ON history.item_id=item.id AND history.date=(SELECT MAX(date) FROM history WHERE item_id=it开发者_开发技巧em.id)

Is MySQL smart enough to execute the SELECT MAX... subquery only once?

Edit: Sorry, I should have mentioned the WHERE on the overall SELECT specifies a singular item id:

WHERE item.id=XXX

Short answer- NO.

Best practice is to use variables

SELECT @maxDate:= MAX(date) FROM history WHERE item_id=item.id

and then to use the variable @maxDate in your queries.

Do explain plan then the query and see

Im going to say No because this is a "correlated subquery"

see : http://blog.sqlauthority.com/2007/09/07/sql-server-correlated-and-noncorrelated-subquery-introduction-explanation-and-example/

I know the DBMS in the blog is MSSQL but the same principle applies