Performance: How to update on duplicate and how to avoid unnecessary queries

Hi! I just started using Yii 2 as my first non-custom framework and I’m trying to figure some stuff out, so here we go:

[size=“4”]Where I’m at[/size]

I have a User model and a Visitor model(both ActiveRecord) and would like to log and display when a user last visited a users profile. What I’m doing now is this:




$userObject = new User();


$profileOwner = $userObject->findOne($userId);


if (!$userId || !$profileOwner) {

    throw new NotFoundHttpException('Invalid user profile.');

}


// Adds to visitor log

$visitorEntry = new Visitor();

$visitorEntry->profile = $profileOwner->id;

$visitorEntry->viewer = \Yii::$app->user->id;

$visitorEntry->time = new Expression('NOW()');

$visitorEntry->save();




$visitors = $profileOwner->getVisitor()->joinWith('user')->all();



[size="4"]Problem #1[/size]

The add to visitor log has a primary key on (profile, viewer), so what I would like in this situation is to run the save() command as an INSERT <…> ON DUPLICATE KEY UPDATE query. I do realize that I can try to get the row first and then just update if it exists, but from an efficiency standpoint that would be a bad decision.

What is the cleanest and best way to make it use the MySQL on duplicate functionality?

[size="4"]Problem #2[/size]

The joinWith(‘user’) results in these queries:


SELECT `visitor`.* FROM `visitor` LEFT JOIN `user` ON `visitor`.`viewer` = `user`.`id` WHERE `visitor`.`profile`=1

SELECT * FROM `user` WHERE `id` IN (1, 2)



Is there any way to avoid the second select and make it become one query like the one below?


SELECT `visitor`.*, `user`.* FROM `visitor` LEFT JOIN `user` ON `visitor`.`viewer` = `user`.`id` WHERE `visitor`.`profile`=1

SELECT * FROM `user` WHERE `id` IN (1, 2)



I do realize that it supports multiple database systems, but I hope there is still some easy and clean way to use such specific features so one don’t have to compromise on database performance.

  1. ON DUPLICATE KEY UPDATE is MySQL specific functionality. AR in general doesn’t support DBMS-specific syntax since it should be the same API no matter if you’re using MySQL or Postgresql or any other supported DB type. So in your case you can either do it as two queries in transaction or use SQL via Yii::$app->db->createCommand($sql)->…

  2. No. It’s how AR works internally.

btw., more queries doesn’t mean less performance in general. Measure resource consumption to be sure it worth optimizing.