(MySQL) Greatest Number By group Inner join , performance problems.

Question

2 tables are still very small compared on how they will be in the future. Already on doing tests on some regular queries , performance problems arise.

Could someone enlighten me on a better table structure or query ?

====

Table 1 : tv_show ( currently 117 rows)

Table 2 : tv_episodes ( currently 43,000 rows ).

tv_show contains an id , which we call t_id on tv_episodes.

tv_episodes contains this t_id and a field called episodes_num.

each episode has an episode number and a season number , episodes_num is the total episode number regardless of the season.

for example : all seasons have 10 episodes: Season 2 episode 1 = episodes_num: 11

Now for the query : I want for each tv show the latest episodes_num.

SELECT tv.*,ep.* FROM tv_show tv
INNER JOIN tv_episodes ep ON ( tv.id = ep.t_id ) 
LEFT OUTER JOIN tv_episodes ep2 
ON ( tv.id = ep2.t_id AND ep.episode_num < ep2.episode_num )
WHERE ep2.t_id is NULL

This works , but gives the server a very hard time ( query above 10 sec!)

Please help.

Edit : MySQL version: *5.1.47

Update : Thanks for al the solutions , this is the one which is the fastest.

    SELECT tv_episodes.* ,tv_show.*

    FROM tv_episodes
    INNER JOIN 
    (
        SELECT t_id, MAX(episode_num) AS episode_num

        FROM tv_episodes
        GROUP BY t_id
    ) max_eps
    ON tv_episodes.t_id = max_eps.t_id AND tv_episodes.episode_num = max_eps.episode_num
  INNER JOIN
 tv_show ON tv_show.id=tv_episodes.t_id



CREATE TABLE `tv_show` (
 `id` int(255) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `specification` varchar(50) NOT NULL,
 `year` int(4) NOT NULL,
 `status` varchar(100) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `name` (`name`),
 KEY `specification` (`specification`),
 KEY `year` (`year`),
 KEY `status` (`status`)
) ENGINE=MyISAM AUTO_INCREMENT=118 DEFAULT CHARSET=latin1





CREATE TABLE `tv_episodes` (
 `id` int(255) NOT NULL AUTO_INCREMENT COMMENT 'e_id',
 `t_id` int(200) NOT NULL,
 `season` int(2) NOT NULL,
 `episode` int(4) NOT NULL,
 `episode_num` int(10) NOT NULL,
 `airing` date NOT NULL,
 `online` enum('1','2') NOT NULL COMMENT '1=yes 2=no',
 `added` int(15) NOT NULL,
 `title` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 PRIMARY KEY (`id`),
 KEY `t_id` (`t_id`),
 KEY `season` (`season`),
 KEY `episode` (`episode`),
 KEY `season_num` (`episode_num`),
 KEY `airing` (`airing`),
 KEY `added` (`added`)
) ENGINE=MyISAM AUTO_INCREMENT=43420 DEFAULT CHARSET=latin1

Answer 1

If you use MSSQL 2005 of higher (you did not specify the database system and version), you can use "APPLY" clausule:

SELECT
    tv.*,ep.*
FROM tv_show tv
CROSS APPLY (
    SELECT TOP 1 * FROM tv_episodes
        WHERE t_id = tv.id
        ORDER BY episode_num DESC
) ep

Answer 2

UPDATE : Try This -

Select tv.name as Tv_Show_Name,Max(episode_num) as Latest_Episode_Number from tv_show tv inner join tv_episode te
on tv.id=te.t_id
group by te.t_id,tv.name,episode_num

Answer 3

Try this:

SELECT tv.*,ep.* FROM tv_show tv
INNER JOIN episodes ep ON ( tv.id = ep.t_id ) 
WHERE ep.episode_num > ALL(SELECT x.episode_num FROM tv_episodes x WHERE x.episode_num != ep.episode_num)

Answer 4

Query performance is not just a function of how the query is structured but also what indexes have been defined on the underlying tables.

Answer 5

Update: Based on your latest comments and my own tweaking, I've edited my answer.

This should return the tv_episode records for each tv_id having the maximum episodes_num

SELECT tv_episodes.*
FROM tv_episodes
INNER JOIN 
(
    SELECT t_id, MAX(episodes_num) AS episodes_num
    FROM tv_episodes
    GROUP BY t_id
) max_eps
ON tv_episodes.t_id = max_eps.t_id AND tv_episodes.episodes_num = max_eps.episodes_num;