I created an application to build up a world for my latest project (a browsergame). For this I need to update about 25.000.000 records within my database.
I realised my algorithm for updating those records was useless as more and more records were worked over. So I thought about dropping those records out, that were not within a certain range or were simply just worked over. For this I needed to create a temporary table and select every single record into within its neighbours to calculate whether something more must be done on this record or not.
For this reason I created a simple stored procedure to fetch all neighbour-world-fields by given position. In a second step I wanted to select the sums of certain field of another table regarding to the fetched neighbour-fields. My first try for a pleasant SQL Statement was like this:
SELECT SUM(field2) FROM another_table
WHERE id IN (SELECT id FROM temporary_table)
As I run this statement it seemed it would never end. Of course there was a Primary Key on another_table field ID but as a matter of fact that was not working.
I figured it must have been something to do with the „in“-clause so just for testing reasons i checked
SELECT SUM(field2) FROM another_table
WHERE id IN (1,2,3)
and it worked fine! Of course I also checked some more difficult „in“-values like 1023,5342 or whatever… But it all worked fine.
I realised it might be, MySQL cannot determine, how many and which ID to fetch from the inner SELECT, but it can from a given mix of distinct IDs.
To put it in a nutshell the best solution for this „in“-problem was to fetch records using a JOIN:
SELECT SUM(field2)
FROM another_table at
RIGHT JOIN temporary_table tmp on at.id=tmp.id
Seems MySQL now can determine, which IDs are to select…
