Hi

There is a movie database. I dont have indexed any field, sigh. But I
dont know if that is the problem. Among the tables there are the
these:

CREATE TABLE `actors` (
`id` int(5) NOT NULL auto_increment,
`the_name` varchar(80) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB ;

CREATE TABLE `scenes_actors` (
`id` int(12) NOT NULL auto_increment,
`scene_id` int(12) NOT NULL,
`actor_id` int(10) NOT NULL,
`priority` int(2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB ;

CREATE TABLE `movies_scenes` (
`id` int(15) NOT NULL auto_increment,
`movie_id` int(10) NOT NULL,
`scene_id` int(3) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB ;

So, I do this query:

SELECT the_name
FROM actors
WHERE id
IN (
SELECT actor_id
FROM scenes_actors
WHERE scene_id
IN (
SELECT id
FROM movies_scenes
WHERE movie_id =269
AND scene_id =1
)
ORDER BY priority
)


This takes about 3 seconds. As this query is put in a loop, that can
be up to 10 entries, the query can take half a minute!

Can someone please have a look at the tables and see what is wrong?

On Tue, 05 Feb 2008 11:43:13 +0100, <warth33@hotmail.com> wrote:

> Hi
>
> There is a movie database. I dont have indexed any field, sigh. But I
> dont know if that is the problem. Among the tables there are the
> these:
>
> CREATE TABLE `actors` (
> `id` int(5) NOT NULL auto_increment,
> `the_name` varchar(80) NOT NULL,
> PRIMARY KEY (`id`)
> ) ENGINE=InnoDB ;
>
> CREATE TABLE `scenes_actors` (
> `id` int(12) NOT NULL auto_increment,
> `scene_id` int(12) NOT NULL,
> `actor_id` int(10) NOT NULL,
> `priority` int(2) NOT NULL,
> PRIMARY KEY (`id`)
> ) ENGINE=InnoDB ;
>
> CREATE TABLE `movies_scenes` (
> `id` int(15) NOT NULL auto_increment,
> `movie_id` int(10) NOT NULL,
> `scene_id` int(3) NOT NULL,
> PRIMARY KEY (`id`)
> ) ENGINE=InnoDB ;
>
> So, I do this query:
>
> SELECT the_name
> FROM actors
> WHERE id
> IN (
> SELECT actor_id
> FROM scenes_actors
> WHERE scene_id
> IN (
> SELECT id
> FROM movies_scenes
> WHERE movie_id =269
> AND scene_id =1
> )
> ORDER BY priority
> )
>
>
> This takes about 3 seconds. As this query is put in a loop, that can
> be up to 10 entries, the query can take half a minute!

Nested subqueries are often not the most efficient. What does this do?
(keep in mind I am a bit confused at why scenes_actors.scene_id apparently
is NOT the same as movies_scenes.scene_id???

SELECT actors.the_name
FROM actors
JOIN scenes_actors
ON actors.actor_id = scenes_actors.actor_id
JOIN movies_scenes
ON movies_scenes.id = scenes_actors.scene_id
AND movies_scenes.movie_id = 269
AND movies_scenes.scene_id = 1

Or the reverse might be more efficient:

SELECT actors.the_name
FROM movies_scenes
JOIN scenes_actors
ON movies_scenes.id = scenes_actors.scene_id
JOIN actors
ON actors.actor_id = scenes_actors.actor_id
WHERE movies_scenes.movie_id = 269
AND movies_scenes.scene_id = 1
--
Rik Wasmus

On 5 Feb, 12:05, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
> On Tue, 05 Feb 2008 11:43:13 +0100, <wart...@hotmail.com> wrote:
> > Hi
>
> > There is a movie database. I dont have indexed any field, sigh. But I
> > dont know if that is the problem. Among the tables there are the
> > these:
>
> > CREATE TABLE `actors` (
> > `id` int(5) NOT NULL auto_increment,
> > `the_name` varchar(80) NOT NULL,
> > PRIMARY KEY (`id`)
> > ) ENGINE=InnoDB ;
>
> > CREATE TABLE `scenes_actors` (
> > `id` int(12) NOT NULL auto_increment,
> > `scene_id` int(12) NOT NULL,
> > `actor_id` int(10) NOT NULL,
> > `priority` int(2) NOT NULL,
> > PRIMARY KEY (`id`)
> > ) ENGINE=InnoDB ;
>
> > CREATE TABLE `movies_scenes` (
> > `id` int(15) NOT NULL auto_increment,
> > `movie_id` int(10) NOT NULL,
> > `scene_id` int(3) NOT NULL,
> > PRIMARY KEY (`id`)
> > ) ENGINE=InnoDB ;
>
> > So, I do this query:
>
> > SELECT the_name
> > FROM actors
> > WHERE id
> > IN (
> > SELECT actor_id
> > FROM scenes_actors
> > WHERE scene_id
> > IN (
> > SELECT id
> > FROM movies_scenes
> > WHERE movie_id =269
> > AND scene_id =1
> > )
> > ORDER BY priority
> > )
>
> > This takes about 3 seconds. As this query is put in a loop, that can
> > be up to 10 entries, the query can take half a minute!
>
> Nested subqueries are often not the most efficient. What does this do?
> (keep in mind I am a bit confused at why scenes_actors.scene_id apparently
> is NOT the same as movies_scenes.scene_id???
>
> SELECT actors.the_name
> FROM actors
> JOIN scenes_actors
> ON actors.actor_id = scenes_actors.actor_id
> JOIN movies_scenes
> ON movies_scenes.id = scenes_actors.scene_id
> AND movies_scenes.movie_id = 269
> AND movies_scenes.scene_id = 1
>
> Or the reverse might be more efficient:
>
> SELECT actors.the_name
> FROM movies_scenes
> JOIN scenes_actors
> ON movies_scenes.id = scenes_actors.scene_id
> JOIN actors
> ON actors.actor_id = scenes_actors.actor_id
> WHERE movies_scenes.movie_id = 269
> AND movies_scenes.scene_id = 1
> --
> Rik Wasmus- Dölj citerad text -
>
> - Visa citerad text -

Oh I dont know how to thank you. it worked perfectly!