Add indexes to speed up queries

[anschluss80]

After a fresh new install of Lessn More 2.2.0 on a MySQL based system the table 'urls' has three indices:

• Primary Key on field 'id'
• checksum_index on field 'checksum'
• redir_type_index on field 'redir_type'

Due to the query issued in /index.php, which uses the field 'custom_url' in the WHERE clause, I suggest to put an additional index there. An alternative is to use the existing and indexed checksum field as the first part in the WHERE clause and after that - due to possible crc32 collisions - the original clause as the second part:
'...WHERE checksum = CRC32(:slug) AND (BINARY) custom_url = ...'
so that MySQL may reduce the possible result set based on the checksum field first and then match against the original WHERE clause in case of checksum collisions.

best rgds,
Alex.

[anschluss80]

Another suggestion:
Until now, I have around 75,000 shrinked URLs and around 3,000 hits (redirects) to date. Calling the stats page will take some remarkable time to load (I guess around 10..15 seconds), even on a big machine. After adding another index to table url_stats on field url_id, the stats page takes no more than a split second to load.

best rgds,
Alex.

[alanhogan]

@lx666 Are you still using Lessn More? Curious to hear more about your usage numbers.

Anyway, I don’t have much time to spend maintaining this project, but if you submitted a PR for the above changes, it would be appreciated

[anschluss80]

@alanhogan Yes, we're still happily using it. Currently there are 1.25 Million shortened URLs and it's serving quite well. The stats page loading time has slowed down to a few seconds, but that's OK regarding the amount of URLs.. :-)

I just submitted a pull request (#28) which will add a new migration (006) to /-/migrations. This migration adds an index on (table-prefix)urls.custom_url which will speed up queries.

Best,
Alex.