I also made an option to use xxh32 as an alternative since it is a faster/more secure algorithm. 631cf56

However, it shouldn't be needed since the function is called only on additions so won't get heavy usage to matter with the speed boost. It also has the downside on upgrade needing to be done in PHP since it can't be done directly in MySQL.

Interesting as always :)

I'll need to run a couple tests on large datasets, I'm always afraid with DB changes on large scales where the upgrade operation might take longer than max_execution_time. You already tested that ?

Another question : how faster is the query, since you're searching at the same time for `url_hash` = :hash AND `url` = :url" ?

I was testing the upgrade with 100,000 URLs in the database and it upgraded basically instantly.

I just did some more tests with 1 million URLs and 4.5 million rows. I found that the order I had the update queries mattered (obvious in hindsight having to calculate indexes on updates vs just after). I pushed a new commit to fix that.

1 million URLs: Upgrade time 9 seconds. (before fix was 28 seconds).

4.5 million URLs. Upgrade time 58 seconds.

So for longer than max_execution_time, I'm not sure. Depending on how many really large database there are out there.

Also as to how faster is the query:
Before hashing:
1 million URLs: About 1 second.
4.5 million URLs: About 8 seconds.

After hashing almost always 0.0001 seconds

The reason that is faster when doing `url_hash` = :hash AND `url` = :url is MySQL will use the index url_hash to find just those records first, and then use a where to search just those records for url.
Alternatively on the normal `url` = :url it uses a where lookup to check every record.

Using just url

Using both url_hash and url

I was thinking more about this and while this does work really well to speed up database inserts with a large URL table, this change is basically only speeding up the yourls_long_url_exists function requests. If you have YOURLS_UNIQUE_URLS set to false then this does nothing at all for you.

From my testing, it is really only useful for setups with over 2 million URLs. Below that amount the performance hit is basically negligible. So, while this does make this fast at any URL list size, I'm not sure if it is worth it for the added complexity of the query and ensuring that hashes are always set on insert/update of URLs just to support those with really large databases.

I think some of it depends on how many people are out there actually running into performance issues like in #3793

The other thought is for those that this would actually help the most, the upgrade does take awhile now. So, we might want to add something in the upgrade that counts the number of URLs that you have and displays a message that since you have so many URLs you should run these SQL commands manually on your server instead of running them automatically.

Maybe, depending on how many people actually need this performance boost, it would be better implemented as a plugin.

As of writing, nearly 86% of people use YOURLS_UNIQUE_URLS, and I'm suspecting the other 14% are not just because there was an option to change :)

What do you mean "ensuring that hashes are always set" ? Do you see a scenario where they wouldn't ? Besides manually writing SQL queries to add entries

What do you mean "ensuring that hashes are always set" ? Do you see a scenario where they wouldn't ? Besides manually writing SQL queries to add entries

Yeah, my thought there was someone manually inserting data to the the database from a bulk insert or something. Or a plugin overriding the add url function and not hashing it.

Without surprise, the very vast majority of (known) YOURLS instances have less than 100K urls -- only 1128 have more, breakdown as follows:

So I'm not too worried about a couple folks hitting a max time exec and filling a support request here.

Plus, the PHP page about setting time limit states something that, if I understand it well, says we're clear :

Note:
The set_time_limit() function and the configuration directive max_execution_time only affect the execution time of the script itself. Any time spent on activity that happens outside the execution of the script such as system calls using system(), stream operations, database queries, etc. is not included when determining the maximum time that the script has been running.

So, to sum it up : I'm in favor of this patch that introduces a DB change to allow for faster lookups & inserts

Letting @dgw and @LeoColomb chime in if they'd like to, as I have the feeling I'm the worst when it comes to DB schema

I'm closing this one since I think #4013 is much better.