[plug] Optimise MySQL query

[simon]

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.

Does mysql do function-based indexes? Youd need an index on MONTH(date) and
user........

If youre not using the data in the returned rows, try doing

select distinct month(date), count(*) from $table where user = $user group by
month(date);

This will return 12 rows with the number of rows against each - be aware tho
that you may have missing months in the results if that user has no rows in
that month.


Note that I dont use mysql much, and my replies are coloured by mainly Oracle
- minor syntax variations may be required :) But it cant hurt to try ;)

--
=================
Simon Scott
simon at chrome64.org
mob: 0409113359
=================