Phalcon Slow Queries

Question

Phalcon Slow Queries

Xtt45 Jul '16

Created Jul '16	Last Reply Jul '16	Replies 4	Views 800	Votes -1

Xtt45 3.7k

Jul '16

MYSQL: Percona Database Server
Webserver: NGINX
OS: Ubuntu Server 14.04

i have the ff table:

CREATE TABLE IF NOT EXISTS `hr_dtr` (
  `dtr_id` int(11) NOT NULL AUTO_INCREMENT,
  `dtr_timedate` datetime DEFAULT NULL,
  `dtr_count` int(11) DEFAULT NULL,
  `dtr_inout` varchar(3) DEFAULT NULL,
  `employee_id` int(11) DEFAULT NULL,
  `employee_pin` varchar(10) NOT NULL,
  PRIMARY KEY (`dtr_id`),
  KEY `fk_hr_dtr_hr_employee1_idx` (`employee_id`),
  KEY `employee_pin` (`employee_pin`),
  KEY `DTR_SEARCH_INDEX` (`dtr_timedate`,`dtr_count`,`employee_id`),
  KEY `dtr_count` (`dtr_count`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=81 ;

with almost 8m rows... and i execute this query

SELECT * FROM hr_dtr WHERE employee_pin = ? AND dtr_timedate = ? ORDER BY dtr_timedate ASC LIMIT 10

in phpmyadmin, it gives the result right away

Showing rows 0 - 29 (2953471 total, Query took 0.0003 sec)

But when i execute it in phalcon, with the same pin and date

$dtr = HrDtr::find(
    array(
        'employee_pin = ?0 AND dtr_timedate = ?1',
        'bind'=>array(
            $pin, $date
        ),
        'order'=>'dtr_timedate ASC',
        'limit'=>10
    )
);

it took 20 to 30 seconds. i don't know what the problem.

1

Nikolay Mihaylov · Answer 1 · 2016-07-29T01:25:11-07:00

Using ORM returns whole objects and it's slower in general. Try using the query builder or raw query.

Your query with the query builder:

$results = $this->modelsManager->createBuilder()
    ->columns([
        'id',
        'title',
    ])
    ->from('HrDtr')
    ->where('employee_pin = :pin:', ['pin' => $pin])
    ->andWhere('dtr_timedate = :date:', ['date' => $date])
    ->orderBy('dtr_timedate ASC')
    ->limit(10)
    ->getQuery()->execute();

Please give it a try and tell us how it went.

Also a good idea is to always select the columns you only need, using select * is a bad idea :)

Wojciech Ślawski · Answer 2 · 2016-07-29T02:37:32-07:00

Select columns which you actually need. As well as work on arrays perhaps somewhere further in code(toArray() method). Also you should basically create indexes on each column which could be possibly used as where - especially on large tables, it should boost performance a bit.

Also are you doing anything with this resultset ? I think it's not query itself, more likely any code that happens after is slowing your app.

Also what php and phalcon version you use ?

Xtt45 · Answer 3 · 2016-07-29T17:56:59-07:00

I have used * because i needed all the columns.

Also i have tried to hydrate it to return only arrays, not objects. It's still the same

      $dtr = HrDtr::find(
              'employee_pin = ?0 AND dtr_timedate = ?1',
              'bind'=>array(
                  $pin, $date
              ),
              'order'=>'dtr_timedate ASC',
              'limit'=>10,
              'hydration'=>Phalcon\Mvc\Model\Resultset::HYDRATE_ARRAYS
          )
      );

Also I have put indexes on columns on the WHERE clause.

PHP Version 5.6.19 Phalcon 2.0.10

I have tried to change the WHERE cause to

    employee_pin LIKE ?0 AND dtr_timedate = ?1

and the pin

    '%'.$pin

seemed to return rows much faster, about 10 seconds. I don't know why. I have explained the MYSQL Query in PHPmyadmin

on the Extra, it said

Using Where, and the filtered was only 11.00, but rows returned was 10.

The previous one was 50.00 filtred and return 10 rows, also Using WHERE.

I just loop through the result set and display it

BadLuckBurt · Answer 4 · 2016-07-29T20:30:10-07:00

If it's still using the WHERE, what does the index you put on those columns look like? If it's like the ones in your original post, I don't think it'll work. Try adding an index that is employee_pin and dtr_time combined like you have done with other columns in DTR_SEARCH_INDEX

Also I have put indexes on columns on the WHERE clause. -snip seemed to return rows much faster, about 10 seconds. I don't know why. I have explained the MYSQL Query in PHPmyadmin

on the Extra, it said

Using Where, and the filtered was only 11.00, but rows returned was 10.

The previous one was 50.00 filtred and return 10 rows, also Using WHERE.

I just loop through the result set and display it

Wojciech Ślawski · Answer 5 · 2016-07-30T01:52:28-07:00

I just loop through the result set and display it

So remove it for instance and check your response time. How exactly you are displaying it ? Maybe try to work on array instead full object like ->toArray() method