joining mysql tables or making the queries run faster

Question

I admit am not the brightest when it comes to joining database tables so would like some help. Currently I have 2 queries, the main one being:

SELECT 
    s.id AS show_id, 
    start_date, 
    end_date
FROM `show` s, `theater_type` tt
WHERE
    s.theater = tt.theater_id AND
    image = 1 AND
    start_date > '2015-05-21' AND
    genre_id IN (1,2,3,12,13,17,21) AND
    tt.type_id IN (1,2,3,4)

This is filtering and returning shows I need. So far it is excellent but for each show I also need its average rating. As a lumberjack I am looping through the results from above and using this query to retrieve the average for each show_id with:

SELECT AVG(rating) AS avgr FROM pro_reviews WHERE show_id = X

This is fine too but often times the first query returns more than 1k of results so the whole process runs more than 10s which is unacceptable for the end user.

I'm looking to join the queries and get it all in one shot. Maybe this won't be faster either but I'm out of options.

This is what I tried, obviously it's wrong because it only returns one row:

SELECT 
    AVG(rating) AS avgr,
    allshows.show_id,
    allshows.start_date,
    allshows.end_date
FROM 
    pro_reviews pr,
    (
        SELECT s.id AS show_id, start_date, end_date
        FROM `show` s, theater_type tt
        WHERE s.theater = tt.theater_id AND image = 1 AND start_date > '2015-05-21' AND genre_id IN (1,2,3,12,13,17,21) AND tt.type_id IN (1,2,3,4)
    ) allshows
WHERE allshows.show_id = pr.show_id

Explain first select returns

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra   
1   SIMPLE  tt  ALL     NULL    NULL    NULL    NULL    209     Using where
1   SIMPLE  s   ALL     NULL    NULL    NULL    NULL    5678    Using where; Using join buffer

What does `explain select...` for your first query says ? Add that into the question. As a side note aggregate function without `group by` always returns one row. — Abhik Chakraborty, May 21 '15 at 07:11
@AbhikChakraborty added explain result. As far as I know if I use group by it will not return rows if I don't have any ratings in the second table for a specific show and I need all even if the average is 0. — slash197, May 21 '15 at 07:23

score 0 · Answer 1 · answered May 21 '15 at 07:23

Create a view

CREATE VIEW all_shows AS
SELECT s.id AS show_id, start_date, end_date

FROM show s, theater_type tt

WHERE s.theater = tt.theater_id AND image = 1 AND genre_id IN (1,2,3,12,13,17,21) AND tt.type_id IN (1,2,3,4);
Then do Your select using this view

SELECT AVG(pro_reviews.rating) AS avgr, all_shows.show_id, all_shows.start_date, all_shows.end_date

FROM pro_reviews

INNER JOIN all_shows ON (all_shows.show_id = pr.show_id)

WHERE all_shows.start_date > '2015-05-21'

GROUP BY pro_reviews.show_id;

Imran · Accepted Answer · 2015-05-21T07:41:39.490

0

SELECT
    AVG(pr.rating) AS avgr
    s.id AS show_id, 
    start_date, 
    end_date
FROM `show` s 
JOIN `theater_type` tt
ON s.theater = tt.theater_id
LEFT JOIN `pro_reviews` pr
ON s.id = pr.show_id
WHERE
    image = 1 AND
    start_date > '2015-05-21' AND
    genre_id IN (1,2,3,12,13,17,21) AND
    tt.type_id IN (1,2,3,4)
GROUP BY s.id

edited May 21 '15 at 07:41

answered May 21 '15 at 07:31

Imran

3,031
4
25
41

Thanks for that but as I said in the comments using `GROUP BY` will eliminate all shows which don't have ratings in the second table – slash197 May 21 '15 at 07:35
@slash197 Ok I understand you comment. For this purpose you just need left join in `pro_reviews` table and you need group by of `s.id` not `pr.show_id` – Imran May 21 '15 at 07:40
And you must add indexes based on `Explain` – Imran May 21 '15 at 07:43
Thanks, that's what I needed. Unfortunately it doesn't help me, this way it's ten times slower than using separate queries. – slash197 May 21 '15 at 07:52
If you add indexes properly its must not slower then separate query . But at first you need to learn how to use index . This [Link](http://stackoverflow.com/questions/3049283/mysql-indexes-what-are-the-best-practices) may help you – Imran May 21 '15 at 08:12
I have indexes for both tables set to primary and unique – slash197 May 21 '15 at 08:43
@slash197 In short , you have to use FULLTEXT [index](https://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html) too. From this [Anwser](http://stackoverflow.com/questions/621884/database-development-mistakes-made-by-application-developers) You may get idea to how to use appropriate indices. Please read more about index . Index is not only primary and unique – Imran May 21 '15 at 09:29

joining mysql tables or making the queries run faster

2 Answers2