Add a url index for faster SQL lookups with large tables#4013
Add a url index for faster SQL lookups with large tables#4013dgw merged 7 commits intoYOURLS:masterfrom
Conversation
|
Smart, thanks, indeed much better |
ozh
left a comment
There was a problem hiding this comment.
Damn, not sure how I missed this :)
|
It happens :) |
|
Glad this is sorted 4 years after I recommended the fix: |
|
For the record this is NOT the fix your recommended @joesenova . To be accurate, this is more the suggestion I made to you 4 years ago, but to which you didn't reply. |
|
Each to their own @ozh . Glad to see that this issue is fixed, thanks for that. |
|
I'm not sure how urgent this is, but there are different index lengths in |
|
Oops, nice catch @2ndK16 |
This is a replacement for the hash index I added in #4006
I found that just adding a basic index to the urls column limited to 30 chars has the same performance boost as the url hash. This handles it all in mysql without adding any new complexity to the codebase or queries.
To note, when doing a lookup by the index it will match on those first 30 characters and to filter it down to matching ones and then do a WHERE on just those results which is significantly faster than doing a WHERE on the entire table. The one drawback vs the hash would be the case where every url in the database is the same domain name that is longer than 30 characters (not a likely case in my opinion) it would just work the same as it does now.
For data size difference, my test DB with over 4 million urls was 1.4 GB with the current version of YOURLS, 1.6 GB with the hash version and also 1.6 GB with this new index version.
Also, the upgrade time was about 1/5 the time of adding the hash in my testing.
Fixes #3793