Hello

I have table BLOG

id, user_id, creation_date, name, content (around 7000 records)

And then I have table SITE_USERS
id, name (around 20000 records)

I want to select the latest blog entries, but one user should not
appear more than once.
Doing:

select
B.*, S.name
from
blog B, site_users S
where
B.user_id = S.id
group by B.user_id
order by creation_date

is not so good. It seems that its not fetching the latest blog entry
from a user (why should it? I dont think I say it in the query, sigh).

So, I tought this query could work:

select
B.*, S.name
from
blog B, site_users S
where
B.user_id = S.id
and
B.id IN (select MAX(id) from blog group by user_id)
order by creation_date

It actually works but takes 20 (!!!!) seconds to execute.

The problem seems to be when joining the two tables. Causa if I remove
from th query the other table (SITE_USERS), then it executes without
problems.
Both tables have been indexed. And have the auto_increment and
primary_key of course.
I really cannot understand why it takes so much time when there is so
little data. Or, well, I guess the subquery must be very bad itself I
guess?

On 2011-02-16 15:52, pac Man wrote:
MySQL is not very good at handling sub selects, but there are other ways
to skin a cat. If you trust your auto increment column as a time
ordering attribute, the latest blog entry per user can be defined as:

select user_id, max(id) as max_id
from BLOG
group by user_id

The result is in it self a table so you can join this with your base
tables like (untested):

select B.*, S.name
from blog B
join (
select user_id, max(id) as max_id
from BLOG
group by user_id
) X
on B.id = X.max_id
join site_users S
on S.id = X.user_id

Note that a construction like:

select B.*, S.name
from blog B, site_users S
where B.user_id = S.id
group by B.user_id

is invalid sql (in general) and should not be used. You can prevent
mistakes like that by enabling ONLY_FULL_GROUP_BY in @@sql_mode.

/Lennart

On 2/16/2011 9:52 AM, pac Man wrote:
So, what's the definition of your merchants table?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

On 2/16/2011 11:37 PM, Jerry Stuckle wrote:
Sorry, got my responses mixed up.

What you need is known around here as the "strawberry query". Google
this group for more information on it.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================