Use INSERT...ON DUPLICATE KEY UPDATE optimization

[ejunker]

Currently, the code uses RouteUsage::updateOrCreate() which first performs a SELECT query and then either an INSERT or UPDATE query. It is doing 2 queries for every request. If using MySQL then we can use INSERT...ON DUPLICATE KEY UPDATE query which will only need to do 1 query and since the identifier column has a UNIQUE index, it will automatically do an update if the identifier already exists.

I don't think Laravel supports those types of queries but you can use a library like:
https://github.com/yadakhov/insert-on-duplicate-key

The code might look something like this:

        $attrs = $this->extractAttributes($event);

        if (config('route-usage.on-duplicate-key-update')) {
            RouteUsage::insertOnDuplicateKey([$attrs]);

            return;
        }

        RouteUsage::updateOrCreate(...);

I assume that doing 1 query instead of 2 would be faster but some testing would need to be done to see if it is actually faster.

UPDATE: I just noticed in #4 that you used to use this type of query and then removed it to support other databases. If you make it a config option then people using MySQL can use the optimization and other databases will continue to use updateOrCreate()

[julienbourdeau]

I'd definitely love to improve it and have a better query for each database. I'm not sure yet what would be the best way.

I was thinking about a macro on the model which registers the correct SQL query depending on your database

[ingalless]

@julienbourdeau I like this idea. I stumbled across a method available on the DB facade that might help with this too?

DB::getDriverName() // e.g. 'sqlsrv'

[ejunker]

Actually, it should be possible to support multiple databases using this package:
https://github.com/staudenmeir/laravel-upsert

From the README

• MySQL 5.1+: INSERT ON DUPLICATE KEY UPDATE
• MariaDB 5.1+: INSERT ON DUPLICATE KEY UPDATE
• PostgreSQL 9.5+: INSERT ON CONFLICT
• SQLite 3.24.0+: INSERT ON CONFLICT
• SQL Server 2008+: MERGE

[julienbourdeau]

i'll have a look 👍

[ejunker]

As of Laravel 8.10 there is built-in support for upsert. laravel/framework#34698