[plug] Optimise MySQL query

[Ryan King]

nigel at dubh.id.au wrote:
> Hi list,
>
> I am counting the total times per month a user is shown in a database with
> code similar to the following;
>
> for ($i=1; $i <= 12; $i++)
> 	$query = "SELECT * FROM $table WHERE MONTH(date) = $i AND user = $user";
> 	$result = mysql_query ($query, $connection);
> 	$rows = mysql_num_rows($result);
>
> 	switch ($i)
> 		case "1":
> 		$jan_total = $rows;
> 		do other stuff for January
> 		break;
> etc...
>
> Now while this works it seems inefficient as we are querying the database 12
> times per user. Can someone suggest a better query to use? Any pointers to
> suggested reading would be appreciated.
>
>   

SELECT count(*), DATE_FORMAT(date, '%Y-%m') as month
FROM $table
WHERE user=$user
GROUP BY month

modify the format string in DATE_FORMAT to suit your needs.

Ryan King