CODEWITHSUNDEEP
mysqlsqlperformance
Ethan Allen
Ethan Allen

Reputation: 14835

Why is this MySQL query taking so long and how can I fix it?

When I have about 30 actors this query is taking 20+ seconds. Is there a way to speed this up dramatically? I am sure there is, I just don't know in what way or where to start.

  REPEAT
    FETCH actors INTO a;
      IF a != '' THEN
        IF !(SELECT COUNT(*) FROM movieactor WHERE actor = a) THEN
          INSERT INTO movieactor (actor) 
          VALUES (a);
         END IF;
      END IF;
  UNTIL done END REPEAT;

movieactor table has just an actor and an id.

Upvotes: 0

Views: 140

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You really need to show the table definitions, indexes, query plan, and triggers to understand performance. However, this query has some obvious suggestions.

Why not use set operations, such as:

insert into movieactor(actor)
    select a
    from actors
    where a <> '' and
          a not in (select * from (select actor from movieactor))

(The double select is something that I think is needed for MySQL to parse this.)

Second, you should have an index on movieactor.actor. That will probably speed up the query a lot.

Upvotes: 1

Related Questions