`YOURLS_UNIQUE_URLS` slows down large instances

[dgw]

Code of Conduct

• I agree to follow this project's Code of Conduct.

Submission validity

• This is not a personal support request, that should be posted on discussions community.
• I checked current issues and this request isn't a duplicate of an existing issue, opened or closed.

Self troubleshooting

• I've read the documentation and made sure to follow it.
• I've read the troubleshooting first steps and frequent issues guides.

Version

1.9.2

Description

As the table of shorturls grows into the millions of rows, if YOURLS_UNIQUE_URLS is enabled (the default), checking whether a new url value already exists in the table takes longer and longer.

Expectation

No response

Reproduction steps

No response

Context

This is a follow-up issue to #3791 in which a user traced slow performance when adding new links on their system to the SQL query run by yourls_long_url_exists(), and found that setting YOURLS_UNIQUE_URLS to false sped up adding new links.

As a first step, I identified three possibilities for making this check faster:

1. Create an index on the url column
   The limitation of this option is that TEXT columns (which url is) must index on a prefix only. With a sufficiently long prefix, it should still improve performance. MySQL should find possible matches using the prefix index, then only perform a full comparison on the candidate rows.
   However, the user in question indicated that they have many, many similar URLs, and the prefix index wouldn't help if the prefix is shorter than the links' common portion.
2. Add a FULLTEXT index on the url column and change the code to use MATCH(url) AGAINST(:url IN BINARY MODE) or similar
3. Add an indexed url_hash column and use that for lookups instead

Options 2 and 3 both require code changes—with 3 being the most drastic. Creating a prefix index (option 1) is the simplest, and should improve performance in most cases with an appropriately-chosen prefix length.

[jarebear6expepjozn6rakjq5iczi3irqwphcvb]

Option 3 sounds like a pretty stout solution that would future-proof and potentially be the most performative, as well. I'd be interested in drafting up a PR for that if it would be worth considering.

[oemilio16]

Hello!

Have you considered migrating the search engine's table(s) from MyISAM to InnoDB in version 1.9.2?

Are there any specific reasons or limitations that prevent such a migration? Understanding these could help me assess the feasibility and potential impact.