mysql multiple row select performance

is there any performance difference between:

SELECT * FROM table WHERE id IN (1,2,3)

versus

SELECT * FROM table WHERE id = 1
SELECT * FROM table WHERE id = 2开发者_StackOverflow社区
SELECT * FROM table WHERE id = 3

whereby you execute each query, so there are 3 total queries to be executed by PHP

Both of the examples provided do different things although they may look similar. Use the construct designed to do what you want. Worrying about "optimization" here is silly and premature: a good model and good design should come first.

The first case is one query -- it returns one result set (of presumably up to 3 items, assuming that ID is a PK). The order of records in the set is not defined as there is no ORDER BY.

The second case has three queries -- and thus three result sets (the order of the result sets in relationship to each-other is defined) -- each result set (assuming again that ID is a PK) will result in 0 or 1 records.

Now, if only it were that simple... depending on whether the second was executed inside a transaction or not (and what isolation level of transaction and what guarantees the backend makes) determines if the same items are guaranteed to be returned in both cases. That is, imagine the record with ID=2 is deleted after the SELECT ID=1 but before SELECT ID=2 -- what should the results be?

All that being said, the "correct" choice is likely the single select, although it is possible to imagine pathological cases where the second is desired. As a bonus, it is also generally easier to deal with a single result set.

I suspect the first case will also be [slightly] better performing just because of the small overheads for query execution and, in any case, it should be no slower than the second. The difference in performance may or may not be negligible depending upon other factors including connection latency. However, the only way to know "for certain" is to run performance tests on actual data/usage and inspect query plans (see EXPLAIN).

Happy coding.

While in this particular example, the difference may be negligible, yes there is a difference.

The first version is 1 query, the second version is 3 queries. Executing more queries usually results in more overhead.

Yes, executing 3 separate queries is going to be a lot slower in this case, especially if indexes aren't involved and you are working with large tables.

There is: each query involve a network roundtrip, going three times through the network will result in more overhead than one for sure.

Will you fetch this ids in other parts of your application?
If you will fetch id = 1 somewhere else, it's better to make separate queries.
Otherwise, if you always need fetch these 3 records together - fetch them in one query.

For example, let's imagine that you need to fetch some data about node. Than you can create function:

getNode($id)
{
  //you can cache each query to make it faster
  if ($node = get_from_cache($id)) return $node;

  //fetch from DB
  $node = ... SELECT * FROM table WHERE id = $id

  //and cache this value
  set_cache($id, $node);

  return $node;
}