php – 使用左连接准确分页

我一直在考虑这个问题,并且我认为最好先问一下并倾听其他人的想法.

我建立了一个在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,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,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,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个钥匙被拿起并订购了！

然后JOIN可以从那里继续,希望只使用10个location_ids.

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

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

以下是您对此方法的其他看法

你如何查询下10个ids(ids 11 – 20)？像这样：

SELECT l.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,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 – 使用左连接准确分页

猜你在找的PHP相关文章