英文:
Python API makes a db call SQL Query Efficiency between = vs IN operator for multiple values
问题
我目前正在从API中进行数据库调用，并且我想确定以下两种方法之间的最有效方法：
使用=运算符的多个查询
select employee_name from employee where employee_id = 123;
select employee_name from employee where employee_id = 456;
select employee_name from employee where employee_id = 789;
使用in运算符的单个查询
select employee_name from employee where employee_id in(123, 456, 789);
目标数据库是MySQL。
英文:
I'm currently working on making a database call from the API and I would like to determine the most efficient approach between the following two:
Multiple queries using the = operator
select employee_name from employee where employee_id = 123;
select employee_name from employee where employee_id = 456;
select employee_name from employee where employee_id = 789;
Single query with in operator
select employee_name from employee where employee_id in(123, 456, 789);
The target database is MySQL.
答案1
得分: 1
Query with IN operator is efficient and provides better performance as compared to individual queries, and this is irrespective of any database that you use.
As you pointed out correctly, The main issue with separate queries is those are executed individually and result in 3 separate round trips from the database which is not great. (Total time includes the time it takes to reach till database server and actual processing for the resultset.)
This is definitely not the case in a query with IN operator because it is a single query to the database. (Plus query plan can be optimized for single query with IN operator)
Hope this helps.
英文:
This is a no-brainer that Query with IN operator is efficient and provides better performance as compared to individual queries, and this is irrespective of any database that you use.
As you pointed out correctly, The main issue with separate queries is those are executed individually and result in 3 separate round trips from the database which is not great. (Total time includes the time it takes to reach till database server and actual processing for the resultset.)
This is definitely not the case in a query with IN operator because it is a single query to the database. (Plus query plan can be optimized for single query with IN operator)
Hope this helps.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论