(no subject)
More mysql goodness.
So I have these 3 tables: categories, albums, pictures.
I want to return the newest picture for each category.
categories has: cid, name
albums has: aid, title, category
pictures has: pid, aid, filepath, filename
(There are a bunch more columns than that, those are just the ones I'm concerned with. Imagine also that they are prefaced with 'cpg_'.)
The relationship is that each picture has an id (pid) and an album id (aid), and that aid is related to the aid in albums, which are all part of categories. So any picture could have a pid of 10, be part of the album 3, and in the category of 2. My problem was finding the right way to do this. At first I tried the kind of round about way:
select cpg_categories.cid, cpg_categories.name, cpg_albums.aid, cpg_albums.title, cpg_pictures.pid as pid, cpg_pictures.filepath, cpg_pictures.filename from (cpg_categories left join cpg_albums on cpg_categories.cid = cpg_albums.category) left join cpg_pictures on cpg_albums.aid = cpg_pictures.aid where cpg_pictures.pid in (select max(cpg_pictures.pid) from cpg_pictures where cpg_pictures.aid = cpg_albums.aid) group by cid;
using left joins I later realized I was basically creating a temporary table with 1800 rows in it that contained the values of 2 other full tables, and even on my dual 3.0 xeon bsd box the query took almost 3 seconds (2.9281 sec to be exact) to return 5 rows!
The nice thing about later version of mysql is the subquery (something people bitched about with 3.23), so I came up with this instead:
select cpg_categories.cid , cpg_albums.title, cpg_albums.description, cpg_categories.name, x.filename, x.filepath from (select * from (select max(pid) as newpid from cpg_pictures group by aid) as y inner join cpg_pictures on cpg_pictures.pid = y.newpid) as x inner join cpg_albums on cpg_albums.aid = x.aid join cpg_categories on cpg_categories.cid = category group by cid order by cid asc;
Basically I get the newest picture and the album it's in (along with filename and path) from cpg_pictures, and group them into the appropriate albums. Subquery runs first and then moves outwards, so it only takes 0.0094 sec to run the subquery, then I assign it to the variable x, inner join those 68 rows (instead of 1800) to the albums, and then joined those to the categories they belonged to, grouped them by their category, sorted by the category so I can display them in the right order and viola! we have our data in 0.0141 secs.
Only took me an entire pot of coffee to get that one right.
So I have these 3 tables: categories, albums, pictures.
I want to return the newest picture for each category.
categories has: cid, name
albums has: aid, title, category
pictures has: pid, aid, filepath, filename
(There are a bunch more columns than that, those are just the ones I'm concerned with. Imagine also that they are prefaced with 'cpg_'.)
The relationship is that each picture has an id (pid) and an album id (aid), and that aid is related to the aid in albums, which are all part of categories. So any picture could have a pid of 10, be part of the album 3, and in the category of 2. My problem was finding the right way to do this. At first I tried the kind of round about way:
select cpg_categories.cid, cpg_categories.name, cpg_albums.aid, cpg_albums.title, cpg_pictures.pid as pid, cpg_pictures.filepath, cpg_pictures.filename from (cpg_categories left join cpg_albums on cpg_categories.cid = cpg_albums.category) left join cpg_pictures on cpg_albums.aid = cpg_pictures.aid where cpg_pictures.pid in (select max(cpg_pictures.pid) from cpg_pictures where cpg_pictures.aid = cpg_albums.aid) group by cid;
using left joins I later realized I was basically creating a temporary table with 1800 rows in it that contained the values of 2 other full tables, and even on my dual 3.0 xeon bsd box the query took almost 3 seconds (2.9281 sec to be exact) to return 5 rows!
The nice thing about later version of mysql is the subquery (something people bitched about with 3.23), so I came up with this instead:
select cpg_categories.cid , cpg_albums.title, cpg_albums.description, cpg_categories.name, x.filename, x.filepath from (select * from (select max(pid) as newpid from cpg_pictures group by aid) as y inner join cpg_pictures on cpg_pictures.pid = y.newpid) as x inner join cpg_albums on cpg_albums.aid = x.aid join cpg_categories on cpg_categories.cid = category group by cid order by cid asc;
Basically I get the newest picture and the album it's in (along with filename and path) from cpg_pictures, and group them into the appropriate albums. Subquery runs first and then moves outwards, so it only takes 0.0094 sec to run the subquery, then I assign it to the variable x, inner join those 68 rows (instead of 1800) to the albums, and then joined those to the categories they belonged to, grouped them by their category, sorted by the category so I can display them in the right order and viola! we have our data in 0.0141 secs.
Only took me an entire pot of coffee to get that one right.