php - Mysql - 每个事件行的位置(高延迟)

Question

我有一个应用程序来控制预订队列，我需要获取每一行的位置。我已经编码了，但是返回数据的延迟非常高(大量查询)。

linePositionTotal - Represents the position based on all active(status) rows.
linePositionSamePeople - Represents the position based on all active(status) rows with the same people number.

<小时/>

表:

:: Reservation
id Int AI
status Tinyint (0-Inactive/1-Active)
people Int
created Datetime

Reservation.php(模型):

class Reservation extends CActiveRecord
{
const STATUS_ACTIVE = 1;

public $linePositionTotal;
public $linePositionSamePeople;

...

public function afterFind() {
    $criteria = new CDbCriteria;
    $criteria->condition = 'created<:created AND status=:status';
    $criteria->params=array(':created'=>$this->created, ':status'=>Reservation::STATUS_ACTIVE);
    $countReservations = Reservation::model()->count($criteria);
    $this->linePositionTotal=$countReservations;

    $criteria = new CDbCriteria;
    $criteria->condition = 'people=:people AND created<:created AND status=:status';
    $criteria->params=array(':people'=>$this->people, ':created'=>$this->created, ':status'=>Reservation::STATUS_ACTIVE);
    $countReservations = Reservation::model()->count($criteria);
    $this->linePositionSamePeople=$countReservations;
}

代码:

$model = Reservation::model()->findAll();
// Problem: If have more than 15 active rows it's taking longer than 10 seconds.

<小时/>

有人知道如何优化它或更好的方法吗？

Answer 1

您正在为找到的每一行运行两个单独的查询。应使用适当的联接将其放入单个 SQL 查询中。您正在查询的数据的架构会很有帮助，但答案将至少涉及一个连接和计数。