- 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
kind_id. It only has entries where seen is > 0.
person_total has the total number of films by each
person_id for given
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.