3 added [query-performance] to 2412 questions - Shog9 (Id=1924)
| link
2 edited title
| link

Difference between DATE(time_field) AND time_field intoin a WHERE clause

1
source | link

Difference between DATE(time_field) AND time_field into WHERE clause

I have 3 tables:

users
movements
unique_ids

I want to get the records gotten from march until now of the users registered in march, counting the unique id of their movements with a distinct and the total of it at the end:

SELECT MONTH(mo.ctime) AS month, COUNT(DISTINCT(unique_id))
FROM users us, movements mo,unique_ids id
WHERE us.time_field > '2014-03-01' AND us.time_field < '2014-03-31 23:59:59'
AND mo.time_field > '2014-03-01'
AND us.room_name LIKE 'name'
AND us.room_name=mo.room_name AND us.user=mo.user AND id.id_log=mo.id_log
GROUP BY month WITH ROLLUP

The thing is that few month ago in other query I realise that adding DATE() to the time_field into WHERE clause makes faster the query, but adding this into the query shown, gives me different results than the initial one.

What's the difference between time_field > 'x' AND DATE(time_field) > 'x' inside WHERE clause?