Mysql - 每个活动行的位置（高延迟）

Question

I have an application to control reservations queue, and I need to get each row position. I already coded, but is getting a very high latency to return the data (heavy queries).

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.

---

Table:

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

Reservation.php (Model):

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

Code:

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

---

Anyone have an idea how to optimize it or a better way to do that?

Answer 1

You're running two separate queries for each row you find. This should be put into a single sql query using appropriate join or joins. The schema for the data you are querying would be helpful, but it the answer will involve at least one join and counts.