Non deterministic functions

[Tobin Harris]

Hi there,

I realise that MySql will not use the query cache for queries containing functions are non-deterministic (i.e - return a different value for each call). However, regardless of the query cache being used, I noticed something else that was odd with the following query...

SELECT *
FROM articles
WHERE body_hash = MD5(LEFT(?body),900)

Here, ?body is a value passed in by the application.

This query is *slow*. However, I managed to decrease query time from 6.5 seconds to 0.3 seconds if I converted it to:

SELECT *
FROM articles
WHERE headline_hash = 'EDd450Fj4DXc120CDFD883SdfdS08D'

This required first doing a query to calculate the hash, and then passing that into the above query. I was surprised that this made a difference, since I'd expect MySQL to do some magic behind the scenes: calculating the hash once, and using that value for each row/field comparison. Is this not the case?

Note that the EXPLAIN reported that the same index was used for each of these two statements. It's just the query time that was much much faster.

Any thoughts/explanations welcome.

Tobin