左联接精确分页 [英] Accurate Pagination with left Joins
问题描述
我已经思考了一段时间了,到了一定程度,我认为最好去问一遍,听听别人的想法.
I've been thinking about this for a while and its got to a point where I think its better to ask around and listen what other people think.
正在建立一个在Mysql上存储位置的系统.每个位置都有一个类型,有些位置有多个地址.
Im bulding a system that stores locations on Mysql. Every location has a type and some locations have multiple addresses.
表看起来像这样
location
- location_id (autoincrement)
- location_name
- location_type_id
location_types
- type_id
- type_name (For example "Laundry")
location_information
- location_id (Reference to the location table)
- location_address
- location_phone
因此,如果我想在数据库中查询最近添加的10个,我将采用类似这样的方法:
So if i wanted to query the database for the 10 most recently added I would go with something like this:
SELECT l.location_id, l.location_name,
t.type_id, t.type_name,
i.location_address, i.location_phone
FROM location AS l
LEFT JOIN location_information AS i ON (l.location_id = i.location_id)
LEFT JOIN location_types AS t ON (l.location_type_id = t.type_id)
ORDER BY l.location_id DESC
LIMIT 10
对吗?但是问题是,如果一个位置的地址多于1个,则限制/分页将不会准确,除非我按L.location_id分组",但是每个位置仅显示一个地址.会发生什么情况.有多个地址的地方?
Right? But the problem is that if a location has more than 1 address the limit/pagination is not going to be accurrate, unless I "GROUP BY l.location_id", but that is going to show only one address for each place.. what happens with the places that have multiple addresses?
因此,我认为解决此问题的唯一方法是在循环内执行查询.类似这样的东西(伪代码):
So I thought the only way to solve this is by doing a query inside a loop.. Something like this (pseudocode):
$db->query('SELECT l.location_id, l.location_name,
t.type_id, t.type_name
FROM location AS l
LEFT JOIN location_types AS t ON (l.location_type_id = t.type_id)
ORDER BY l.location_id DESC
LIMIT 10');
$locations = array();
while ($row = $db->fetchRow())
{
$db->query('SELECT i.location_address, i.location_phone
FROM location_information AS i
WHERE i.location_id = ?', $row['location_id']);
$locationInfo = $db->fetchAll();
$locations[$row['location_id']] = array('location_name' => $row['location_name'],
'location_type' => $row['location_type'],
'location_info' => $locationInfo);
}
现在即时通讯获得了最后10个位置,但是通过这样做,我至少可以查询10个以上的查询,而且我认为这不会提高应用程序的性能.
Now im getting the last 10 places, but by doing that I wind up with at least 10 queries more, and I dont think that helps the app performance.
是否有更好的方法来实现我所寻找的目标? (准确的分页).
Is there a better way to achieve what im looking for? (accurate pagination).
推荐答案
这是您的原始查询
SELECT l.location_id, l.location_name,
t.type_id, t.type_name,
i.location_address, i.location_phone
FROM location AS l
LEFT JOIN location_information AS i ON (l.location_id = i.location_id)
LEFT JOIN location_types AS t ON (l.location_type_id = t.type_id)
ORDER BY l.location_id DESC
LIMIT 10
您最后执行分页.如果重构此查询,则可以更早地执行分页.
You perform the pagination last. If you refactor this query, you can perform the pagination earlier.
SELECT l.location_id, l.location_name,
t.type_id, t.type_name,
i.location_address, i.location_phone
FROM
(SELECT location_id,location_type_id FROM location
ORDER BY location_id LIMIT 10) AS k
LEFT JOIN location AS l ON (k.location_id = l.location_id)
LEFT JOIN location_information AS i ON (k.location_id = i.location_id)
LEFT JOIN location_types AS t ON (l.location_type_id = t.type_id)
;
注意,我创建了一个名为k
的子查询.拿起10个键并首先订购!!!
Notice I created a subquery called k
. The 10 keys get picked up and ordered FIRST !!!
然后JOIN可以从那里继续,希望仅使用10个location_id.
Then the JOINs can go on from there, hope using just 10 location_ids.
对子查询k
有帮助的是一个包含location_id和location_type_id的索引
What will help the subquery k
is an index that carries location_id and location_type_id
ALTER TABLE location ADD INDEX id_type_ndx (location_id,location_type_id);
您可能还会喜欢这种方法
Here is something else you may like about this approach
如何查询接下来的10个ID(11-20)?像这样:
How do you query for the next 10 ids (ids 11 - 20) ? Like this:
SELECT l.location_id, l.location_name,
t.type_id, t.type_name,
i.location_address, i.location_phone
FROM
(SELECT location_id,location_type_id FROM location
ORDER BY location_id LIMIT 10,10) AS k
LEFT JOIN location AS l ON (k.location_id = l.location_id)
LEFT JOIN location_information AS i ON (k.location_id = i.location_id)
LEFT JOIN location_types AS t ON (l.location_type_id = t.type_id)
;
您要做的就是用每个新页面更改子查询k
中的LIMIT
子句.
All you have to do is change the LIMIT
clause in subquery k
with each new page.
-
LIMIT 20,10
-
LIMIT 30,10
- 依此类推...
LIMIT 20,10
LIMIT 30,10
- and so on...
我可以通过删除位置表来改善重构,并让子查询k携带所需的字段,如下所示:
I can improve the refactoring by removing the location table and have subquery k carry the needed fields like this:
SELECT k.location_id, k.location_name,
t.type_id, t.type_name,
i.location_address, i.location_phone
FROM
(SELECT location_id,location_type_id,location_name
FROM location ORDER BY location_id LIMIT 10,10) AS k
LEFT JOIN location_information AS i ON (k.location_id = i.location_id)
LEFT JOIN location_types AS t ON (k.location_type_id = t.type_id)
;
此版本不需要额外的索引.
Making that extra index would not be necessary for this version.
尝试一下!
这篇关于左联接精确分页的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!