MySQL query help - tie in 3 tables data

[ochaos]

Hi there,

I need some help seeing if I can clean up this query to be faster. Currently, it table about 6.5 seconds and if I can speed it up, I'd love that.

3 tables.
Styles - This table is a list of styles and each of these styles needs to be listed on a page with some other data from the ensuing tables.
Important table fields.
Style_id, Style_name
Approximate Records: 120

Beers - This tables lists all the beers, breweries associated with them and the beer's respective style.
Important table fields.
Beer_id, Style_id
Approximate Records: 25K

Beer_Consumed - This table lists all beers consumed and also has a field for the style_id listed in Styles.
Important table fields.
consumed_id,style_id
Approximate Records: 50K

What I'd like to do is create a MySQL query that will list each style_name while also listing the number of beers for that style and how many consumed beers for that style in the same result set.

Here's what I have that taking 6.5 seconds.
SELECT style_name,
(
SELECT COUNT(beer_id)
FROM Beer
WHERE Beer.style_id=style.style_id
)
AS beers,
(
SELECT COUNT(consumed_id)
FROM Beer_Consumed
WHERE Beer_Consumed.style_id=Style.style_id
)
AS consumed
FROM Style ;

 

[towerbase]

Your query took about 11 seconds on my PC. The following query produces the same results in about 0.25 seconds:

SELECT style_name, SUM(beer_count), SUM(consumed_count)
FROM
( 
  SELECT style_id, style_name, count(*) as Beer_count, 0 as Consumed_Count
    FROM styles
    JOIN beers using(style_id)
  GROUP BY style_id
 UNION
  SELECT style_id, style_name, 0, count(*) as Consumed_count
    FROM styles
    JOIN Beer_consumed using(style_id)
  GROUP BY style_id
) AS merged
GROUP BY style_id

Note that you were inconsistent with the way you named your tables for example you referred to Styles and Style.

There are possibly better solutions - wait for Rudi to supply the best one!

Andrew
Hampshire, UK

 

[ochaos]

Thanks Andrew,

Your solution cuts down my time a lot, I've gone from 6.5+ seconds to just under 1 second with your changes..

Also, sorry about the inconsistent data, it's an old DB that has some weird naming between tables and I was trying to make it presentable.

I'll go with this route unless there is something more efficient like you mentioned. I'm down to about .8 seconds now.

Thank you for your help!

Mike