Index on short code does not work if domain is null.

[marijnvandevoorde]

Shlink version

4.3.0

PHP version

8.4

How do you serve Shlink

Self-hosted RoadRunner

Database engine

MicrosoftSQL

Database version

12.0

Current behavior

Our Shlink instance holds over 1.6M records right now and redirects became really, really slow. We did some digging and noticed that we hadn't been setting a domain to the short urls. That means that the short_urls.domain_id was always null. The index that is used for the redirect spans both columns. This is correct, but because the domain_id column is first, the index is not used in our case.

Expected behavior

The index should also be used in case no domain is set. This could be achieved by switching the two columns around, this fixes our issue. It does not degrade performance for other usecases. You now first check on the column with high cardinality (very unlikely to have multiple identical values, almost impossible). In the edge case where two identical shortcodes are present, only a few rows will be left (same shortcode but for different domains).

(Which makes me realise, by the way: What if the same shortcode is presetn with null as domain and then a specific domain. I would expect it to take the explicit one over the non explicit one as behviour, and that's probably how it works. But we don't have that usecase, so not an issue for us)

Minimum steps to reproduce

• Enter a ton of records in your DB with null as domain_id
• do a search and check the execution plan. Notice how the index is not used.

[acelaya]

Good spot! I'll try to get it sorted.

(Which makes me realise, by the way: What if the same shortcode is presetn with null as domain and then a specific domain. I would expect it to take the explicit one over the non explicit one as behviour, and that's probably how it works. But we don't have that usecase, so not an issue for us)

It will pick up the one corresponding to the visited domain. If the default domain is visited, it will pick up the entry where domain is null, if the other domain is visited, then it will pick the one explicitly set for that domain.

There's a more detailed explanation here to know how different combinations would work, in case you are curious https://shlink.io/documentation/advanced/multiple-domains/#visits

[marijnvandevoorde]

Makes 100% sense, it's what I would expect, if we had that usecase. All good! And thanks for picking this up, much appreciated!

[acelaya]

Apparently this only affects Microsoft SQL (I was getting confused as I was seeing the index used in the other engines).

The index defines the columns in the right order here:

shlink/module/Core/config/entities-mappings/Shlinkio.Shlink.Core.ShortUrl.Entity.ShortUrl.php

Line 91 in eff4f1f

$builder->addUniqueConstraint(['short_code', 'domain_id'], 'unique_short_code_plus_domain');

But then this migration deletes and recreates it in the wrong order, only for Microsoft SQL:

shlink/module/Core/migrations/Version20230130090946.php

Lines 30 to 32 in eff4f1f

	$this->connection->executeStatement(
	'CREATE INDEX unique_short_code_plus_domain ON short_urls (domain_id, short_code);',
	);

I guess it should be a matter of recreating it again with the columns in the right order, but I need to check how long would that take for instances with a lot of short URLs like in your case.

[acelaya]

I need to check how long would that take for instances with a lot of short URLs like in your case.

It takes milliseconds for 1M short URLs, so I think it's fine.

[acelaya]

I have just released v4.3.1 which includes a fix for this.