Query::one uses fetch instead of limit - big memory usage

[SonicGD]

Model::find()->one();

generate

SELECT * FROM ModelTable;

and mysql return all records from table. Then yii use fetch to return first row of result. But all result already in memory.

In our project we have table with 77 million records. Such query use 20.83 gb of memory. But if we add limit(1) it use only 3.5mb.

I think one() should set limit(1) something like this:

public function one($db = null)
    {
        return $this->limit(1)->createCommand($db)->queryOne();
    }

I can submit PR.

[samdark]

Please do. This one seems quite important since it's used a lot.

[klimov-paul]

Method one() assumes you have composed query condition to match single row - not the whole batch. Additional limit may slow down such simple queries like:
SELECT * FROM ModelTable WHERE id = 1
I don't think automatic limit is appropriate here.

[samdark]

@klimov-paul are you sure SELECT * FROM ModelTable WHERE id = 1 LIMIT 1 can be slower than SELECT * FROM ModelTable WHERE id = 1?

[klimov-paul]

In this particular simple example - probably not. But in complex query with several joins addition of limit may cause SQL server to use a temporary table to store data before limit cut - this can dramatically increase the time of query execution.

[SonicGD]

But what would happens often - complex query with join or simple query, where limit doesn't affect performance? I was sure that it uses limit, we have many places with one() based on this opinion.

This behavior can be specified in documentation, but it will cause high memory usage every time and this is not good.

[klimov-paul]

LIMIT, GROUP BY, ORDER BY, HAVING should NOT be applied automatically without any options.

[nineinchnick]

I'd rather get an error about exceeded memory usage and fix the query than have it silently execute for a very long time.

There should probably be a check if there is more than one record returned from the db to avoid high memory usage.

[samdark]

@nineinchnick it depends on if you have enough memory on your server. If you do it will just execute for a long time.

[SonicGD]

@nineinchnick by default php-cli have memory limit = 0. So query just eat all memory, all swap and hang whole server. I prefer one slow query instead.

But if auto-limit is such bad thing to implement, what can we do? Add a string to docs? Or add param to one()?

public function one($db = null, $useLimit=false)
{
    if($useLimit) $this->limit(1);
        return $this->createCommand($db)->queryOne();
}

However user still should know about "fetch" behavior

[samdark]

@klimov-paul how about making it use limit by default while allowing to avoid it via flag-argument?

[SonicGD]

@samdark @klimov-paul yes, this is even better