MySQL select COUNT() accross tables using WHERE statement?

Question

So far I got it working with a subselect, but some research told me it is bad to be using subselects (especially on large tables) as they are less performant.

Now that's what I got:

SELECT COUNT( riddims.riddim ) AS rc, 
(
    SELECT COUNT( tunes.tune )
    FROM tunes
    WHERE tunes.tune NOT
    IN (
        ''
    )
) AS tc
FROM riddims
WHERE riddims.riddim NOT
IN (
    ''
)

The tables look something like:

riddims:
riddim | genre | image

tunes:
riddim | artist | tune

I was playing around with 'JOIN' but couldn't realy figure a working query out. What I'd need is something similiar to STACKOVERFLOW COUNT FROM MULTIPLE TABLES in a more performant way than my above solution.

My goal is to perform a query that shows following output:

riddims | tunes | artist
100     | 400   | 2

WHERE riddims NOT IN ('')
WHERE tunes NOT IN('')
WHERE artist = 'some artist'

This is how I started but its obviously going into the wrong direction:

SELECT COUNT(riddims.riddim) AS rc, COUNT(tunes.tune) AS tc FROM riddims LEFT JOIN tunes ON riddims.riddim = tunes.riddim

Well, it really depends on how you designed your tables. But in your case, you need to create a subquery on it. Joining tables will result you invalid records for this. as *i think* they have no relationship with each other. — John Woo, Sep 12 '12 at 01:45
tables are designed like above, both the RIDDIMS and TUNES table have 3 columns like described above, all type of VARCHAR(255). so there is no other way but using a subquery in my case? — phew, Sep 12 '12 at 02:00

score 1 · Accepted Answer · answered Sep 12 '12 at 02:39

Are you trying to do this:

select riddims, tunes, artists
from (select count(*) as riddims from riddims where . . . ) r cross join
     (select count(*) as tunes from tunes where tunes not in  . . .) t cross join
     (select count(*) as artists from tunes where artist not in . . .) a

Your tables don't seem to be connected, at least for this query. The possible performance issue is that the subqueries in your select are being called once for each row. By putting them in the FROM clause, you eliminate this possible problem.

MySQL select COUNT() accross tables using WHERE statement?

1 Answers1