MySQL Best way to select using list of ID's

Question

Through a webservice my application receives a list of identifiers. With this list, I have to look up a field, one per identifier. If the field does not exist, the value should be null (it has to be shown).

I am wondering what would be the best approach. First I thought it was best to create a temporary table holding the id's and then joining it to the table holding the data, but if I'm correct this takes at least 1 query per identifier to insert it in the temporary table. In that case, it seems that I could just as well iterate through the list of identifiers in my application and query the database 1 by 1. Is this correct? Which approach can you advise?

greetings,

coen

Answer 1

Use the SELECT WHERE IN() Syntax to get a result set with the data you need, then iterate over it in your code. This way you only query the DB once and only get the information you need.

Answer 2

Showing nulls is the trick, you need to join the table to itself, so there are two index lookups per record. Just doing a 1-to-1 query for each identifier will only require one index lookup.

In practice, it won't be twice as slow, since the identifier will be in the key cache by the time the second lookup is executed.

Another option is to render your output using the input identifiers, and use an "IN" like previously suggested. The null records won't be in the query output, but that would be ok since you know what was requested.