I'm working on a algorithm to explore different connections between two data sets and I am wondering how smart InnoDB is with memory usage in regards to temporary tables.
I have a tree of conditions to explore:
C1 / \ C2 C3 / | | \ C3 C4 .....
My idea for exploring the tree would be to first create a temporary table, tempC1, from my data where C1 is matched. Then one, tempC1C2, from tempC1 where C2 is matched. Then two, tempC1C2C3 and tempC1C2C4, from tempC1C2 where C3 and C4 are matched respectively.
I got the idea of creating temporary tables since I'll need to do multiple selects for each table in order to score that particular combination of conditions but I am a little worried about memory usage.
Can I expect InnoDb to be smart enough and not store a bunch of duplicates in memory? Will an other storage engine give me better results?