Conversation
…ook and changed the Data Type of url column to VARCHAR(1024) from TEXT TEXT is not a performance firndly data type. I changed the version(s) app and db - to 1.7.4 and 483 The upgrade applies a patch on the database table yourls_url on column url We haev notice a drastric decrease in db load.
|
This might not be such a good idea. URLs have no maximum length, so any upper bound on the size of that column would be asking for "bug" reports that YOURLS truncates links. Even if this is something we want:
|
|
The reason why you see a reduced load is that you added an index on the URL field I think. I chose TEXT because of the absence of practical limitation on URL length, knowing that this would be a trade-off with speed. I wouldn't recommend indeed going lower than 2048 as @dgw said, but this won't be possible as a general patch for YOURLS : 90% of people don't care about reducing load on their shorteners, and we've already had issues with too long URLs. We've had people for instance storing base64 encoded data URL. This said, I think we can leave this discussion open because people better than me in SQL may have ideas on how to improve performance while keeping arbitrary long URLs |
|
Hi yes, we see a reduction of load on the COU from average 60% to 6% on normal day to day activities. The TEXT datatype is a very expensive datatype to do queries on. You can not add index on TEXT as it has no length ro index on. Everytime you do a query it scans all the text fileds. Therefore using a big varchar data type helps with indexing and query speed/performance. What we can do is add a field in the admin part of the UI to customize thr size of the Varchar datatype. Size wise. This will enable users to change the size on the fly. Once you start doing these queries you see a read total of 290K rows/s. Anyway let see if we can come to a consolidated solution that will help with this load issue. Then for the version update, I just did what I thought was necessary for the upgrade to work. I should only have changed the database version. |
|
This pull request does indexing on the correct col to improve the DB performance and load on the server(s). We have been running this code on our production environment for the past 5 months and working like a charm. We have more than 10mil keys, I can also now use one ui for multiple DB's(SB's on the same server). And all is well now. |
|
@joesenova |
|
I'm closing this PR because, due to the limitation of URL length, it won't go into core. This said, I think there may be a different approach: it's possible to index TEXT if you specify the key length. It won't give a unique index but I'm curious to see the benefits. See https://stackoverflow.com/a/2889835/36850 Is this something you've considered @joesenova ? |
We ran into heavy load issues on our databases(AuroraSQL), we had a look and changed the Data Type of url column to VARCHAR(1024) from TEXT
TEXT is not a performance firndly data type.
I changed the version(s) app and db - to 1.7.4 and 483
The upgrade applies a patch on the database table yourls_url on column url
We haev notice a drastric decrease in db load.