2

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.

  • 1
    Try adding an index on (role_id, person_id, kind_id, seen) and (role_id, person_id, kind_id, total) respectively - that way the group by can be resolved without a filesort and the indexes are covering.. Your second table is missing the primary key, so an internal one is created (it is mandadory for InnoDB). Maybe some combination of (role_id, person_id, kind_id) is always unique so might be used that way? – jkavalik Dec 26 '15 at 19:14
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.