Best practice regarding temporary tables used for join lookups

[Jens Teglhus Møller]

Hi

I recently ran into an issue where query performance on our mysql database degraded badly because we used the "in" operator with 200+ values.

As a better solution for doing lookups on many values I started looking into creating a temporary table (with keys and all) that I would populate with my values and then join.

So instead of doing: select * from table where key in (... many many values ...) I would do:

create temporary table

insert many many values in temporary tables

select from table join on key with temporary table

My question here is how jooq can help me best? I think it would be smart if I could define a class with the table structure in advance (including keys and foreign keys, like the output from the code generator) and then use it on both createTemporaryTable, insert and join().onKey().

Can anyone recommend this or perhaps an alternative approach?

Best regards Jens