MySQL IMDB Query Speed
Alias notation
Your table aliases a1/a2 and c1/c2 are not good. In this very short query it may not matter, however names for aliases, variables, parameters, etc. should be descriptive enough to say something about what they represent. Besides, with 4-5 character table names, is it really that much to type? I would use Actor1/Actor2 and Cast1/Cast2.
In fact, given the nature of your query, you could give them even more useful names... how about MaleAct/MaleCast and FemAct/FemCast!
Old style JOIN
This JOIN syntax should not be used:
FROM Actor AS a1, Actor AS a2, Cast AS c1, Cast AS c2
WHERE c1.mid = c2.mid AND 
      a1.id = c1.pid    AND 
      a2.id = c2.pid    AND 

Instead:
FROM Actor AS MaleAct
    INNER JOIN Cast AS MaleCast ON MaleAct.id = MaleCast.pid
    INNER JOIN Cast AS FemCast ON MaleCast.id = FemCast.id
    INNER JOIN Actor AS FemAct ON FemAct.id = FemCast.pid

Multiple counting
I see you COUNT(*) 3 times within your query. Since you're already selecting the COUNT(*) into your result set, and MySQL will allow you to use column aliases in GROUP BY, ORDER BY, or HAVING clauses.
This:
HAVING COUNT(*) &gt; 0
ORDER BY COUNT(*) DESC;

Instead:
HAVING num_films &gt; 0
ORDER BY num_films DESC;

That should speed up your query a bit too.
Everything together
SELECT MaleAct.fname, MaleAct.lname, FemAct.fname, FemAct.lname, count(*) AS num_films
FROM Actor AS MaleAct
    INNER JOIN Cast AS MaleCast ON MaleAct.id = MaleCast.pid
    INNER JOIN Cast AS FemCast ON MaleCast.id = FemCast.id
    INNER JOIN Actor AS FemAct ON FemAct.id = FemCast.pid
WHERE
      MaleAct.gender = 'M' AND
      FemAct.gender = 'F'
GROUP BY MaleAct.id, FemAct.id, MaleAct.fname, MaleAct.lname, FemAct.fname, FemAct.lname,
HAVING num_films &gt; 0
ORDER BY num_films DESC;