Limiting results per criteria

[MSRez]

At the moment I have a script that runs through links in several categories and orders them with the most recent first. The links are added at different times so some categories have more recent links than others. However, I want the same number of links from each category to be displayed, say 20, so I can't just limit the total number of links because the categories will not be balanced.

My previous solution was to query each category individually to obtain the results but with a large number of categories this is not efficient. So my question is, how can I focus this into one query and limit the number of results per category.

The important table details are:

Categories
---
cat_id

Links
---
link_id, cat_id

cat_id in both are linked together.

Many thanks.

 

[r937]

you missed one important detail -- how do you know which link is more recent than which other link?

r937.com | rudy.ca

 

[MSRez]

Sorry, that's just using link_time stored in UNIX time format - ORDER BY link_time DESC

 

[r937]

try this, it gives last 10 links per category --

select a.cat_id
     , b1.link_id
     , b1.link_time
  from categories as a
inner
  join links as b1
    on b1.cat_id = a.cat_id
inner
  join links as b2
    on b2.cat_id = a.cat_id
   and b2.link_time >= b1.link_time
group
    by a.cat_id
     , b1.link_id
     , b1.link_time
having count(*) <= 10   
order
    by a.cat_id
     , b1.link_time desc

r937.com | rudy.ca

 

[guelphdad]

but ... but .... they wanted 20! </smart a$$ comment>

 

[r937]

yeah, i know ;-)

r937.com | rudy.ca

 

[MSRez]

Thanks for posting. I'm getting some joy with the SQL statement as it does appear to be doing what I intended but for some reason it's running very, very slowly. Any ideas why?

Thanks

 

[r937]

insufficient indexing is likely the reason

what indexes have you declared?

r937.com | rudy.ca

 

[MSRez]

link_id and cat_id are primary keys and cat_id for the links table is an index too. I generally just index fields that are the most important part of the query or ones that are used to join to other tables.

Oh, and when I say slow, I mean uncharacteristically slow. It takes minutes for the page to return.

 

[r937]

try a compound index on (cat_id,link_time) in the links table

r937.com | rudy.ca

 

[MSRez]

It's still taking 20 odd seconds!

 

[r937]

just out of curiosity, how many categories, and how many links are there?

r937.com | rudy.ca

 

[MSRez]

At the moment, 19 categories and about 10,000 links.