Index for short_code + domain_id ignored when finding short URLs in (at least) MS SQL

[acelaya]

Shlink version

All

PHP version

N/A

How do you serve Shlink

N/A

Database engine

MicrosoftSQL

Database version

12

Current behavior

As reported in #2285 (comment), the index for short_code and domain_id ends up not being used when fetching a single short URL, which makes it quite slow as the amount of short URLs grows.

This is likely related with the fact that the domain_id is nullable, but needs to be verified.

We also need to check if/how other database engines are affected, as the reporter has verified this with Microsoft SQL specifically.

The query used to fetch a single short URL during redirects might also need to be adjusted to use the index.

Expected behavior

In instances with a couple million short URLs, visiting one should result in a "fast" redirect.

Minimum steps to reproduce

Create a couple million short URLs, then visit one of them to notice resolution is slow.

There are more details in the comment liked above.

[marijnvandevoorde]

In azure data studio, you can ask to show the plan (which is pretty much MS's version of explain). That will show you the index is ignored.