We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

to get rows very slow

Hi,guys

I need to print all rows from mysql // Get all robots $robots = Robots::find();

// Alternative way to count the resultset echo $robots->count();

I use this way go get rows. but very slow. My database about have 100k card number rows

about 50k status is used and 50k is active

I need to list rows The data following

all card_number total all card_number_active_total all card_number_used total today used_card_number_total

print last 30days per day used_card_number total

per user today used_card total per user used card total (all)

system users total(all) system users total(active) system users total(disabled)

Thanks

<?php

class Cards extends \Phalcon\Mvc\Model

{

/**
 *
 * @var integer
 * @Primary
 * @Identity
 * @Column(type="integer", length=50, nullable=false)
 */
public $id;

/**
 *
 * @var string
 * @Column(type="string", length=50, nullable=false)
 */
public $password;

/**
 *
 * @var string
 * @Column(type="string", nullable=false)
 */
public $import_date;

/**
 *
 * @var string
 * @Column(type="string", length=10, nullable=false)
 */
public $card_status;

/**
 *
 * @var integer
 * @Column(type="integer", length=10, nullable=false)
 */
public $import_user;

/**
 *
 * @var integer
 * @Column(type="integer", length=10, nullable=false)
 */
public $operate_staff;

/**
 *
 * @var string
 * @Column(type="string", nullable=false)
 */
public $operate_date;

/**
 * Initialize method for model.
 */
public function initialize()
{
    $this->setSchema("card_system");
    $this->belongsTo('import_user', '\Admin', 'id', ['alias' => 'Admin']);
    $this->belongsTo('operate_staff', '\Users', 'id', ['alias' => 'Users']);
}

/**
 * Returns table name mapped in the model.
 *
 * @return string
 */
public function getSource()
{
    return 'cards';
}

/**
 * Allows to query a set of records that match the specified conditions
 *
 * @param mixed $parameters
 * @return Cards[]|Cards|\Phalcon\Mvc\Model\ResultSetInterface
 */
public static function find($parameters = null)
{
    return parent::find($parameters);
}

/**
 * Allows to query the first record that match the specified conditions
 *
 * @param mixed $parameters
 * @return Cards|\Phalcon\Mvc\Model\ResultInterface
 */
public static function findFirst($parameters = null)
{
    return parent::findFirst($parameters);
}

}

Thanks

I know that now.

because query number of daily use for the last 30 days need do 30times query so slow

Use limit with SQL_CALC_FOUND_ROW and FOUND_ROWS() then you be able to get the total count of your query without parsing all data and use a pagination in your to let get the data page by page.
https://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_found-rows

  1. Robots::find() it will use full model, when you will access any row from it - it will hydrate model and return object of your class - it will be heavy operation on many records
  2. Best is to select columns you only need, in many sitautions user don't need to know all the data and sometimes it's even safer this way
  3. Best is to not work at objects at all - use toArray method and operate on array when possible.
  4. Pagination is also way to go - limit/offset in most cases should be enough, but if you have like really huge table like going around hundred of thousands/milions of rows or more, then try to avoid offset and use where condition + limit if possible.