英文:
Sqlx WHERE IN query with long list
问题
我正在使用sqlx在我的Go代码中执行查询。查询中有一个包含很多值（约10,000个）的列表，我需要在WHERE IN (?)子句中进行筛选。这导致性能严重下降。我该如何优化这样的查询？此外，这个查询是在一个Redshift集群上执行的，所以对列进行索引并不能解决查询性能的问题。
values := []int64{143, 123, 123, 542....} // ~10,000个元素
query, args, err := sqlx.In(query, values)
if err != nil {
return nil, err
}
query = dbInterface.Rebind(query)
err = dbInterface.Select(&list, query, args...)
查询语句：
SELECT * FROM table_name WHERE some_id IN (?) ORDER BY created_at;
英文:
I'm using sqlx to perform a query in my Go code. The query has a long list of values (~10,000) that I need to filter on in the WHERE IN (?) clause. This causes a huge slowdown in performance. How can I optimize such a query? Also, this query is being performed on a Redshift cluster, so indexing a column isn't a solution to improve the query performance.
values := []int64{143, 123, 123, 542....} // ~10,000 elements
query, args, err := sqlx.In(query, values)
if err != nil {
return nil, err
}
query = dbInterface.Rebind(query)
err = dbInterface.Select(&list, query, args...)
Query:
SELECT * FROM table_name WHERE some_id IN (?) ORDER BY created_at;
答案1
得分: 0
你需要让 sqlx 生成其中一种语法：
-
使用
values语法：where some_id in (values (143), (123)) -
使用数组连接：
select * from t inner join unnest(array[143, 123]) u (id) on u.id = t.id
英文:
You need to make sqlx produce one of these:
-
the
valuessyntax:where some_id in (values (143), (123)) -
join an array:
select * from t inner join unnest(array[143, 123]) u (id) on u.id = t.id
答案2
得分: 0
我最终创建了一个临时表格来存储这些id，并进行了连接操作。这大大提高了查询性能。
英文:
I ultimately created a TEMP TABLE for the ids and performed a JOIN. This greatly improved the query performance.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论