sql语句中当条件的数量非常大时where...in条件子句用什么更好的方法代替?
来源:互联网 发布:聪明的网络语怎么说 编辑:程序博客网 时间:2024/05/02 01:38
http://bbs.csdn.net/topics/390137775/
EXPLAIN EXTENDED
How to create fast database queries
Passing parameters in MySQL: IN list vs. temporary table
with 4 comments
When you need to pass a list of parameters into a MySQL, there are at least two method to do this:
- Issue a dynamical query which would fill an
IN
list with constant values - Fill a temporary table with these values then use it in a
JOIN
The second method is considered better for many reasons (you can easily reuse the values, it's more injection-proof etc.)
Leaving all these issues aside, let's compare these methods performance-wise.
We will create a table of 50,000 records, pass a list of 500 parameters (using both methods) and see which is faster:
Table creation details
We have created two stored procedures.
The first procedure, prc_temporary
, fills the temporary value using a dynamic query then issues the query using a JOIN
in a loop.
The second procedure, prc_range
, just uses the IN
list in a dynamic query, also in a loop.
Let's run both procedures:
1.
CALL prc_list(1000)
1.
CALL prc_temporary(1000)
However, if we change the procedures and leave but 20 parameters:
Changing procedures here
, both procedures have almost the same performance:
1.
CALL prc_list(1000)
1.
CALL prc_temporary(1000)
We see that for a large list of parameters, passing them in a temporary table is much faster that as a constant list, while for small lists performance is almost the same.
Using a temporary table is the best way to pass large arrays of parameters in MySQL.
4 Responses to 'Passing parameters in MySQL: IN list vs. temporary table'
- sql语句中当条件的数量非常大时where...in条件子句用什么更好的方法代替?
- ireport中当使用connection方式获得数据源时如何构造sql语句的where条件。
- sql的条件子句
- oracle中sql语句中的in的条件数量大于1000有问题
- sql语句中过滤条件where和having的区别
- 使用where子句查询表中满足条件的记录
- mysql的5中查询子句之一where条件查询
- 【转】ACCESS中如何在SQL语句的WHERE条件中加日期条件
- sql语句中如何根据不同的条件,执行不同的where条件
- SQL--表达式,条件语句,运算和WHERE子句 (2)
- SQL-SELECT 语句,From子句,where条件查询
- 当WHERE子句、GROUP BY子句和HAVING子句同时出现在一个查询中时,SQL的执行顺序
- SQL中on条件与where条件的区别
- SQL中on条件与where条件的区别[转]
- SQL中on条件与where条件的区别(转载)
- SQL中on条件与where条件的区别[转]
- SQL中on条件与where条件的区别
- SQL中on条件与where条件的区别
- HTML5的 input:file上传类型控制 与上传多文件
- java线程的生命周期及五种基本状态(转载)
- C内存管理
- 我的前端页面国际化的三种尝试(谷歌搜索、translater.js、 jquery i18n)
- 函数返回临时变量的问题和左值和右值
- sql语句中当条件的数量非常大时where...in条件子句用什么更好的方法代替?
- CSDN爬虫(六)——动态网页爬取的两种策略
- CSS面试(简答)
- WPF中,回车触发按钮点击事件
- hadoop集群namenode启动不了问题
- android处理listview的item被双击
- SOA入门及了解
- android中属性动画 property Animation
- [Magent SQL] 查询 还没有被分类的产品SKU(查询有哪些产品还没有设置分类)
You could provide an SQL example
Alon
24 Apr 13 at 15:51
@Alon: click on the links (“Table creation details”) and (“Changing procedures here”)
Quassnoi
24 Apr 13 at 16:03
Isn’t mysql internally creating a temp table even with IN (LIST) syntax? If you perform an EXPLAIN on any query with the IN list statement, you will see the “using temporary” message. Based on that I would think there has to be more to this performance difference. Perhaps the temp table that Mysql is using also has a unique index on the values, which would avoid redundant / duplicate joins and save on query time but lose on time to create the unique temp table values. It would be interesting to see the results of your test if you also added unique indexing on your temp table. I would think the methods would be on par at that point.
Mike
30 Jun 13 at 12:03
Not sure where is a bottleneck in your procedure,
but IN (with,constants, here) may not be a culprit.
Maybe indexes?
I just run a query with some 2100 constants (yes, 2 zeros – all integers) on a temp table 11.000 rows and it took some 0.4 sec to complete.
I must add, that query had 7 tables involved 2 of which were LEFT JOINed and it was quite heavy with filters: multiple BETWEENs,”=”, even some SELECT GROUP_CONCAT() subqueries.
On top of above, query run BOOLEAN FULLTEXT search on 2 tables, which is not speediest of them all.
0.4 sec is not that bad.