Page 1 of 1

mysql month()

Posted: Fri Nov 21, 2003 10:10 am
by xisle
Looking for the most efficient mysql query for month comparison -
I am pulling from tables with minimum 200k records and performing multiple queries for building site useage reports.

Do you know a more efficient way to write this query?

Code: Select all

$query = "SELECT COUNT(DISTINCT user_id) as monthusers FROM Activity WHERE MONTH(last_visit)=MONTH(NOW())";
thanks in advance..

Posted: Fri Nov 21, 2003 10:35 am
by twigletmac
One possible caveat with the approach you've taken - won't that, for example, pick all last_visits from November regardless of the year the visit is made in?

Mac

Posted: Fri Nov 21, 2003 10:40 am
by xisle
aye good point, that isn't going to work.. I am ultimately trying to get away from 'last_visit LIKE '{$YearMonth}%'

Posted: Fri Nov 21, 2003 10:56 am
by xisle
Looks like I can avoid a 'LIKE' with a date_format(). Let me know if there are any other ideas..

Code: Select all

$query = "SELECT COUNT(DISTINCT user_id) as monthusers 
FROM Activity 
WHERE DATE_FORMAT(last_visit, '%Y%m')='{$YearMonth}'";