A basic tip in MySQL when you have to make multiple calls to the same table with a number of different ID's is to use the IN operator. If you had a list of ID's which you want to get the record for instead of making multiple calls to the same table I can combine these into one database call by using IN.
SELECT * FROM foo f where f.id IN (1, 2, 3);
This will return a recordset of these rows like the following.
+----+--------+
| id | name |
+----+--------+
| 1 | first |
| 2 | second |
| 3 | third |
+----+--------+
3 rows in set (0.00 sec)
But the problem with this call is that the order you pass these ID's into the IN statement doesn't make any difference to the way it is returned. If I had a list of ID's 2, 3, 1 and wanted to get the records for this ID's I can then use the IN statement to get these records.
SELECT * FROM foo f where f.id IN (2, 3, 1);
The returned recordset will use the primary key to order the records and it will come back exactly the same as the first call. But I want the returned recordset to maintain the order in which I provided the SQL and return in this order.
+----+--------+
| id | name |
+----+--------+
| 2 | second |
| 3 | third |
| 1 | first |
+----+--------+
3 rows in set (0.00 sec)
To maintain the order of the IN statement I have to set the order I want it to be return by using the ORDER BY FIELD(), which allows me to specify the field and values I want it to be returned.
SELECT * FROM foo f where f.id IN (2, 3, 1)
ORDER BY FIELD(f.id, 2, 3, 1);