Help with limit claus

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Help with limit claus

Post 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.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

it's a tad bit hard to say without knowing the table structure and some example data and a query.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

What does that query give you? It looks like it should give you at least something for each id - correct?
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
Post Reply