mysql 两列上的值相同的OR查询的索引

qrjkbowd  于 2023-06-04  发布在  Mysql
关注(0)|答案(1)|浏览(179)

我有以下用例:
我的表(重要部分):

CREATE TABLE transfers (
    id int,
    product_id int,
    source_location_id int,
    destination_location_id int,
    quantity int,
    created_at datetime
);

现在我的查询是:

SELECT * 
FROM transfers 
WHERE (source_location_id = 123 OR destination_location_id = 123)
ORDER BY created_at DESC
LIMIT 100 OFFSET 400

目前,该表包含2000万行,此查询需要2-3分钟才能完成)
有没有一种方法可以为OR子句创建一个索引,因为这个特定的id对于SELECT中的两个位置总是相同的。
我看到一些人提到了OR子句的联合,但我使用的ORM不支持UNIONS也不支持SUBQUERIES,而且联合不支持分页。

jgzswidk

jgzswidk1#

不,没有索引可以帮助这一点。要对这个查询进行索引,你需要一个联合,最好像这样:

SELECT transfers.* 
FROM transfers 
JOIN (
    (
        SELECT id, created_at
        FROM transfers 
        WHERE source_location_id = 123
        ORDER BY created_at DESC
        LIMIT 500
    )
    UNION DISTINCT
    (
        SELECT id, created_at
        FROM transfers 
        WHERE destination_location_id = 123
        ORDER BY created_at DESC
        LIMIT 500
    )
) ids USING (id)
ORDER BY ids.created_at DESC
LIMIT 100 OFFSET 400

如果source和destination不相同,则应使用UNION ALL。
或者,添加另一个表,如:

CREATE transfer_locations (
    transfer_id int,
    location_id int,
    created_at datetime,
    primary key (location_id, transfer_id)
)

其中包含每个传输的源和目标行,并从中选择连接传输。

相关问题