Page 1 of 1

Help with limit claus

Posted: Wed Apr 12, 2006 4:53 am
by s.dot
I have the following query:

Code: Select all

$b_result2 = mysql_query("SELECT `userid`,`time` FROM `blog` WHERE `userid` IN($ids_list) GROUP BY `userid` ORDER BY `id` DESC");

while($b_array2 = mysql_fetch_assoc($b_result2)){
	echo '<pre>';
	print_r($b_array2);
	echo '</pre>';
}
And this gives me what I want.

So the problem?

What I want to do is select the last blog entry for each id in $ids_list. Only ONE entry. Is this query selecting all the entries?

Say userid 10 has 8394 blogs. Is that all being fetched by this query?

When I throw a LIMIT 1 onto the end of the query it only returns one result... not one result per userid.

Posted: Wed Apr 12, 2006 8:31 am
by feyd
it's a tad bit hard to say without knowing the table structure and some example data and a query.

Posted: Wed Apr 12, 2006 2:12 pm
by pickle
What does that query give you? It looks like it should give you at least something for each id - correct?

Posted: Wed Apr 12, 2006 3:18 pm
by s.dot
Yes, it does give me the correct information.

It retrieves the last blog entry from each user in $ids_list, which is want I want.

But I fear that it's selecting ALL the blogs from each user and using more resources than it should. If I put LIMIT 1, it limites the whole query.

Posted: Wed Apr 12, 2006 3:31 pm
by pickle
Ah - gotcha.

If I had to guess, I would guess that that MySQL is retrieving all the rows, which it then groups. However, the query result you get back, won't have any extraneous information.

Posted: Wed Apr 12, 2006 4:37 pm
by RobertGonzalez
Have you timed your query to see how it is performing? You know, depending on your MySQL version, you may be able to run a subquery to this to try to optimize your data selection.