1

I have a number of calls which are similar. Below is one:

$STH = $DBH->prepare("
SELECT t.mobile, t.unum
FROM teacher t
LEFT JOIN s_group_member m ON m.unum = t.unum
LEFT JOIN s_group g ON g.gnum = m.gnum
WHERE m.gnum = :g AND g.unum = :u
  AND t.unum NOT IN (SELECT unum FROM t_unav WHERE unav_date = :d)");

It gets data from the teacher table where (1) the teacher is a member of a specified group, (2) the group belongs to the user, and (3) the teacher is not unavailable.

My question concerns which will prove quickest:

  1. Make the sub-select call each time.
  2. Cache the sub-select into an array, and then use IN($array) like in this post.
  3. Create a temporary table.

A similar call is made a fair few times - maybe 20-30...

Thanks.

Community
  • 1
  • 1
Nick
  • 5,995
  • 12
  • 54
  • 78

1 Answers1

2

The subquery in your example should be MATERIALIZED which means that it will be executed just ONCE (mysql will create automatically a tmp table to store the results from the subquery).

Subquery materialization using a temporary table avoids such rewrites and makes it possible to execute the subquery only once rather than once per row of the outer query. Materialization speeds up query execution by generating a subquery result as a temporary table, normally in memory. The first time MySQL needs the subquery result, it materializes that result into a temporary table. Any subsequent time the result is needed, MySQL refers again to the temporary table.

But if the results are not big from the subquery you can indeed put them in array, it should be a little faster.

Also you can look at EXISTS optimization here

Stephan
  • 8,000
  • 3
  • 36
  • 42