我已经考虑了一段时间，现在我认为最好四处询问并听听其他人的想法。

我正在构建一个在 Mysql 上存储位置的系统。每个位置都有一个类型，有些位置有多个地址。

表格看起来像这样

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 个，我会使用如下方法:

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 个地址，限制/分页将不会准确，除非我“GROUP BY l.location_id”，但这将只显示每个位置的一个地址..会发生什么有多个地址的地方？

所以我认为解决这个问题的唯一方法是在循环中进行查询。像这样(伪代码):

$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 个查询，我认为这对应用性能没有帮助。

是否有更好的方法来实现我所寻找的？ (准确分页)。

最佳答案

这是您的原始查询

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 

您最后执行分页。如果重构此查询，则可以更早地执行分页。

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 把 key !!!

然后 JOIN 可以从那里继续，希望只使用 10 个 location_id。

有助于子查询k的是一个带有location_id和location_type_id的索引

ALTER TABLE location ADD INDEX id_type_ndx (location_id,location_type_id);

您可能还喜欢这种方法

您如何查询接下来的 10 个 ID(ID 11 - 20)？像这样:

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 子句。

• 限制 20,10
• 限制 30,10
• 等等...

我可以通过删除位置表并让子查询 k 像这样携带所需的字段来改进重构:

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) 
;

此版本不需要创建额外的索引。

试一试!!!

关于php - 左连接的准确分页，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/9282854/