costas
(Cpartassides)
November 18, 2013, 11:38am
1
I was reading this webpage about upgrading from 1.1 to 2.0 and this cought my eye :
https://github.com/yiisoft/yii2/blob/master/docs/guide/upgrade-from-v1.md
When loading relational records in an eager way, Yii 2.0 does it differently from 1.1. In particular, in 1.1 a JOIN query would be used to bring both the primary and the relational records; while in 2.0, two SQL statements are executed without using JOIN: the first statement brings back the primary records and the second brings back the relational records by filtering with the primary keys of the primary records.
Wouldn’t that be slower ?
So what happens if i want to load 500 primary records and 10 detail records for each primary. Will yii execute
1 query for 500 and then 1 query for each 500 records = 501 queries ?
Can someone elaborate on this ?
C.
No.
So what happens if i want to load 500 primary records and 10 detail records for each primary. Will yii execute
1 query for 500 and then 1 query for each 500 records = 501 queries ?
Can someone elaborate on this ?
C.
No.
Btw, some db engines like MySQL restricts max number of values inside IN(…), this should be taken into account.
Also, you still can use a join (for example, if you need filtering by related column).
http://www.yiiframework.com/forum/index.php/topic/47032-ar-and-joins/
UPD: my bad, misread. Updated
softark
(Softark)
November 18, 2013, 1:21pm
3
No, Yii will use 2 queries when it’s an eager loading: one query for the main 500 records, and another query for the related 500 x 10 records. In the 2nd query Yii will use the PKs retrieved in the 1st query, using IN conditional clause.
costas
(Cpartassides)
November 18, 2013, 1:33pm
4
softark:
No, Yii will use 2 queries when it’s an eager loading: one query for the main 500 records, and another query for the related 500 x 10 records. In the 2nd query Yii will use the PKs retrieved in the 1st query, using IN conditional clause.
Yes but in the 2nd query i will have 500 filtering parameters. Wouldn’t that be too much for MySQL and drop the query ?
C.
softark
(Softark)
November 18, 2013, 1:42pm
5
It depends on your environment, I think.
They say that "max_allowed_packet" will limit the number of parameters.
I think you’ll hit the memory limit of PHP before you hit that of MySQL.