Subquery efficiency when used in IN

[azzazzello]

Hi,

Suppose I have the following query

SELECT sum(some_value) from some_table s
where s.date in (date1,date2,date3)

Suppose I want to make it contingent on some other table and basically do the following

SELECT sum(some_value) from some_table s
where s.date in ( select distinct(date) from other_table o where o.update_time >= ( select max(update_time) from some_table ))

Lets say that the subquery when ran by iself takes 0.3 of a second. Let's say there are 60K records in some_table. What I seem to be experiencing is that the select with subquery takes an INORDINATE amount of time. Is it because the nested subquery has to be reran for EVERY row? The result is static - I'd love for it to just be run once. Can this be done without 2 queries and temporary tables?

Thank you!

 

[feherke]

Hi

Using sub-[tt]select[/tt] in the [tt]where[/tt] clause is the less efficient, especially when the [tt]in[/tt] operator is involved. The quantum of its inefficiency depends on the amount of data returned by the sub-[tt]select[/tt].

If you want a quick rewrite, just move the sub-[tt]select[/tt] to the [tt]from[/tt] clause :

[b]select[/b]
sum[teal]([/teal]some_value[teal])[/teal]

[b]from[/b] some_table
[b]inner[/b] [b]join[/b] [teal]([/teal]
  [b]select[/b] [b]distinct[/b]
  date

  [b]from[/b] other_table
  [teal],([/teal]
    [b]select[/b]
    max[teal]([/teal]update_time[teal])[/teal] [b]as[/b] max

    [b]from[/b] some_table
  [teal])[/teal] bar
  [b]where[/b] o[teal].[/teal]update_time [teal]>=[/teal] bar[teal].[/teal]max
[teal])[/teal] foo [b]using[/b] [teal]([/teal]date[teal])[/teal]

Note that I not optimize MySQL queries regularly, the above query may have errors.


Feherke.

http://free.rootshell.be/~feherke/