Joining two fast subqueries is very slow

Question

• DB: MySQL 5.5.20 (WampServer)
• OS: Win 7
• HDD: Western Digital 3TB Caviar Green, 3.5", IntelliPower, 64MB, Sata3 (WD30EZRX)
• Memory: 8GB
• my.ini: http://pastie.org/private/s7l9aadyi0qhfl5bcrnnqq

I've got two tables:

CREATE TABLE `person_seen` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `person_id` int(11) NOT NULL,
 `role_id` tinyint(3) unsigned NOT NULL,
 `kind_id` tinyint(3) unsigned NOT NULL,
 `seen` smallint(5) unsigned NOT NULL,
 PRIMARY KEY (`id`),
 KEY `person_id` (`role_id`,`kind_id`,`person_id`)
) ENGINE=InnoDB AUTO_INCREMENT=63501 DEFAULT CHARSET=utf8

CREATE TABLE `person_total` (
 `person_id` int(11) NOT NULL,
 `role_id` tinyint(3) unsigned NOT NULL,
 `kind_id` tinyint(3) unsigned NOT NULL,
 `total` smallint(5) unsigned NOT NULL,
 KEY `person_id` (`role_id`,`kind_id`,`person_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

person_seen has the number of films I've seen from each person_id for given role_id and kind_id. It only has entries where seen is > 0.

person_total has the total number of films by each person_id for given role_id and kind_id.

Roles are things like director, writer, actor, etc. Kinds are things like movie, tv movie, tv series, etc.

The user should be able to choose the exact combination of values they want to access.

One such query I'm trying to run is:

SELECT name.id, name.name, ds.is_favorite, ds.is_disliked, 
    ds.is_watchlist, ps.seen, pt.total
FROM (
    SELECT person_id, SUM( seen ) AS seen
    FROM person_seen
    WHERE role_id = 8 AND kind_id IN ( 1, 3, 4, 5 )
    GROUP BY person_id
) ps
LEFT JOIN (
    SELECT person_id, SUM( total ) AS total
    FROM person_total
    WHERE role_id = 8 AND kind_id IN ( 1, 3, 4, 5 )
    GROUP BY person_id
) pt ON pt.person_id = ps.person_id
LEFT JOIN name ON name.id = ps.person_id
LEFT JOIN person_status ds ON ds.person_id = ps.person_id

You can see EXPLAIN of the query here: http://pastie.org/private/tv9cp9y6pfa44glzynuowa

The query means:

Get a list of all directors (role_id=8) I've seen at least one film from (movie, tv movie, video movie, tv mini-series), their total number of films for those categories, their names, and if I have them in my favorites/dislikes/watchlist.

The slowness of the query is JOINing the two subqueries. By slow I mean no patience to wait for it to return anything.

I've tried creating VIEWs for those subqueries but JOINing them is still equally slow.

Running those subqueries separately is very fast though, below 0.1 seconds.

Answer 1

This pattern is very slow:

FROM ( SELECT ... )
JOIN ( SELECT ... )

especially when both subqueries have lots of rows. This is because there are no indexes to speed up the "join". MySQL 5.6 will create the optimal index (on the fly), but you appear to be using an older version.

Suggest upgrading.