Code: Select all
mysql> select va.anime_id, anime, count(vid_id) as "count" from video_anime va, anime a where va.anime_id = a.anime_id group by anime_id order by "count" desc limit 10;
+----------+-------------------------+-------+
| anime_id | anime | count |
+----------+-------------------------+-------+
| 7 | Dragonball Z | 1577 |
| 27 | Neon Genesis Evangelion | 1076 |
| 789 | Dragon Ball Z | 1011 |
| 46 | Cowboy Bebop | 916 |
| 24 | Trigun | 779 |
| 8 | Final Fantasy 8 | 701 |
| 6 | Rurouni Kenshin | 523 |
| 29 | Sailor Moon | 487 |
| 166 | Final Fantasy 9 | 457 |
| 19 | Gundam Wing | 385 |
+----------+-------------------------+-------+
PS: MySQL really hates that join. The join --- which has a freakin' primary key --- increases query time by an order of magnitude (estimate). Fun, fun, fun.