Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

Is Relationship increase load on mySql

If I used relation in model like belongsTo, hasMany etc. in model and use object to get matched data from another table. Will it increase load on mySql Server, Since it call single query for each result from resultset



48.3k

The relationship models are lazy-loaded so just selecting a model or group of models won't affect MySQL. If you add model validators though that perform integrity checks then it would very marginall increase performance, but no worse than doing the checks yourself. At the end of the day maintaining referential integrity takes a bit of CPU to do so there is no way around that.

Lets say that you use a magic method on the model to query for the related records. I have a model called Hats and there is a has-many relationship to Ribbons. I've defined this relationship with the ribbons alias on the Hats model. To query them it would look like this $hats->getRibbons(). The getRibbons PHP magic method is just calling the find method on the Ribbons model and passing into that static method a parameter or two to narrow down those records in the database. This is lazy-loaded so it takes the same amount of time if you explicitly call it from the base model or if you were to call it with Ribbons::find(....). Its really just better in every way to define the relationship and not to worry about CPU usage in that way. If there is a major performance consideration then Phalcon has a very robust caching system that can scale from just memory to many different types of backends like Memcache, Reddis, etc. If you aren't using those caching systems already then it probably doesn't matter in your case at all.

But Suppose I have multiple Hats and each Hats have ribbons. So If I queried for hats only then will it queried for each ribbons inside each hats when I used object of ribbons? e.g. I have 10 hats and each 10 hats have 2 ribbons, and I m using ribbons object each time while diplay. Then will it call 1 query for hats and 20 queries for ribbons. So will it not increase request to database. So it is preferrable method?



48.3k

It will do 1 query for ten hats and 10 queries for two hats each. So 1+10 queries. Then, if you want to be able to access the hat from the ribbons model it might do another query to obtain that (but I'm not sure).

You should read about the caching system in Phalcon. You could set your caching system to store every hat and ribbon as it comes in and/or as the entire query itself. I'm just getting started with the caching system so I'm not sure. Someone just gave me this bit of code to get started with caching:

$di->set('modelsCache', function () {
    return new \Phalcon\Cache\Backend\Memory(new \Phalcon\Cache\Frontend\Data);
});
Hats::find(['cache' => ['key' => 'all-hats']]);


48.3k
edited Feb '15

Well set your relationships up for sure and they are lazy-loaded so they will only be used if you call them with $hat->getRibbons(). If you need to be real efficient in some case then call $hats->find() and then get the keys from that resultset and then query for all ribbons that match Then you can be down to 1+1=2 queries only but then you have to do some extra PHP work to match the ribbons up with the hats.. I think that its better to setup some caching though, unless the data is changing a lot. You could always just invalidate some cache in your API if those related records changed.



861
Accepted
answer

You can write join query, so that you have to execute only one query. You will get your hat and its ribbons in one query