using COUNT for several subqueries

[fivestringsurf]

MySQL client version: 5.1.41I'm curious to know if there's a better/more efficient way to do this:

I have 2 tables:

class: stores genre/artist/album information in a child-parent relational way

song: simply stores song titles and links them to their class id (album)

 

so the table "class" might look like this:

/// class
class_id 	parent_id 	name
	1 	0 		rock
	2 	0 		classical
	3 	0 		country
	4 	1 		pearl jam
	5 	1 		blues traveler
	6 	4 		ten
	7 	4 		vitalogy
	8 	5 		four
	9 	5 		travelers and thieves
	10 	3 		garth brooks
	11 	10 		greatest hits

 

...and the table "song" might look like this:

// song
song_id 	class_id 	title
	1 	6 		even flow
	2 	6 		jeremy
	3 	6 		alive
	4 	9 		the tiding
	5 	9 		mountain cry
	6 	8 		nothingman
	7 	8 		whipping
	8 	8 		bugs
	9 	8 		better man
	10 	11 		friends in low places

 

I need to report each genre name and the total number of songs per genre

The following query is what I came up with and it works, but when used in larger data samples it takes a couple minutes to render the results.

SELECT
name,
(SELECT COUNT(song.title)
FROM song
WHERE song.class_id
	IN
	(SELECT album.class_id
	FROM class AS album
	WHERE album.parent_id 
		IN
		(SELECT artist.class_id
		FROM class AS artist
		WHERE artist.parent_id = genre.class_id
		)
	)
)
AS song_count
FROM
class AS genre
WHERE
genre.parent_id = 0

//output
name 	song_count
rock 	9
classical 	0
country 	1

 

What is a better way to do this?

What am I overlooking?

Why is it taking so long?

[The Little Guy]

Give this a try:

 

select c.*, count(*) as total from class c left join song s on(c.class_id = s.class_id) group by c.class_id;

[fivestringsurf]

thanks for the reply @thelittleguy

 

unfortunately your query yields this:

class_id 	parent_id 	name 			total
1 		0 		rock 			1
2 		0 		classical 		1
3 		0 		country 		1
4 		1 		pearl jam 		1
5 		1 		blues traveler 		1
6 		4 		ten 			3
7 		4 		vitalogy 		1
8 		5 		four 			4
9 		5 		travelers and thieves 	2
10 		3 		garth brooks 		1
11 		10 		greatest hits 		1

 

 

I was hoping to list just the "genres" with the total song count for each.

got any other ideas?Thanks again

[The Little Guy]

Looks like I was grouping by the wrong table column, this should give you just the name and the total as well:

 

select c.name, count(*) as total from class c left join song s on(c.class_id = s.class_id) group by s.class_id;

[fivestringsurf]

getting closer!

 

that last query yeilds this:

name 			total
rock 			7
ten 			3
four 			4
travelers and thieves 	2
greatest hits 		1

 

when I need this:

name 	song_count
rock 	        9
classical 	0
country 	1

 

i've never used the "group by" clause so it looks like ive got some more learning to do after i get this kink ironed out...

 

 

edit:

I just attached the insert statements for this sample if that will help

 

 

[attachment deleted by admin]

[The Little Guy]

as I look at this, I ask why did you mix artists and genres into one table?

 

You should separate the two to make life easier on yourself.

 

A small break down:

- database = collection of information

- table = a grouping of related data (t1: song, t2: artist, t3: album, t4: genre, etc)

 

A table should not contain a mixture of two different types of data, aka genre and artist.

This is the starting process of "Database Normalization" (The process of reducing redundant data) this will make searches faster and easier to write.

light reading: http://en.wikipedia.org/wiki/Database_normalization

 

I didn't see you doing this, but just to keep in mind:

1 item = 1 record (do not store 4 items in one record as a comma separated value, because a MySQL database is NOT a CSV file)

[fivestringsurf]

thanks for the info... i appretiate it all.

not sure what you mean by:

1 item = 1 record (do not store 4 items in one record as a comma separated value, because a MySQL database is NOT a CSV file)

 

But I guess I should provide a little bit more info.   The whole "music" db was a quick thing I built to test queries (and ask my question here...believe it or not).  The real database i am using this with is for tracking menu classes (parent/child) relationships and items that reside under them.  I used the album/artist/genre to simulate this.  This table is build that way(id/parnen_id) so it can support infinite parent/child relationships. 

 

Yes- if this was a simple music db (like the one i provided) I would without a doubt have a table for each "category"

 

If you look at my original post the code I am using does work but it is unacceptably slow - not sure why. (maybe it makes some giant cartesion product of something)