2

In case I only provide a single value, are the following sql statements equivalent (eg in terms of performance)?

SELECT * FROM mytable where lastname IN(:lastnames);
SELECT * FROM mytable where lastname = :lastname;

Background: I have service that should serve a list, and a service that serves a single result. Now I thought why creating two database query endpoints, if I could achieve the same thing with just one query (means: also a single result could be queried by using the IN clause).

membersound
  • 81,582
  • 193
  • 585
  • 1,120
  • Not sure on mysql syntax (been a while) but yes you can have only 1 value in an IN statement, and the 2 above will return the same results) – Brad Jul 31 '19 at 12:14
  • Don't even think about it. Use a single query if this is possible. The difference in performance (if any) would be microseconds. – forpas Jul 31 '19 at 12:21
  • 1
    For single value result will be marginally same. In query will actually work as OR condition internally and you're passing only one value so it will execute in same way. If you're passing multiple values in IN query then it will slow it down as it is work as OR condition and that have a bad performance in the SQL – Ashok Gadri Jul 31 '19 at 12:24

3 Answers3

0

i tried it on my mariaDB database on a small table with hundred of records and the query with IN is a bit slower than the first one (which is to be expected) but we are talking of 0.02 sec difference

Benoit F
  • 479
  • 2
  • 10
0

Assuming your db's engine is optimised and would check if there is one value inside the IN parameters and "convert" it to an equal/do the correct operation it would still be technically longer than just a written equal.

Also see this about IN performance.

pmaldera
  • 157
  • 7
-1

Use This Query. It May Be Solve Your Problem.

SELECT * FROM mytable where lastname IN(SELECT lastname FROM mytable where lastname = :lastname);

Vinothkumar Palanivel
  • 1