Official Mys v1.4.0 Ideas & Suggestions Thread

[IntoRain]

Originally Posted by Kyttias

For v1.4.0, I'd like to see some database optimization done. Some of the VARCHAR values could easily be held as CHAR values.

There are trade-offs, both with their benefits. VARCHAR values take up less disk space, because while they hold a maximum value, they store the contents dynamically. But it's because of this reason exactly that they query slower. CHAR values will always take up the exact amount of space you allocate, whether they are filled or not. But because they are predictable in this fashion, queries through them run up to 20% faster. On the downside, again, is that they are storing the full length of the max you set, regardless of how much of it is filled.

For values that will barely change in length, useralternates in the owned_adoptables table, for example, CHAR(3) may be a better fit, given it will only ever hold yes/no values (and at that rate, rewrite the code to handle it in binary, perchance?), or gender, as a better example, it only holds one character, easily could be CHAR1 instead of VARCHAR10. I think the entire database could use a look over before release, trim up some absurd lengths here and there, etc.

It comes down to size on disk versus query speed. Some more reading here?

But if there's going to be any importing from previous versions, there could be conflicts... =/

There isn't really that much of a difference in terms of speed, it's pretty neglectible honestly. I believe the char/varchar query differences are actually if you manually create the indexes based on those columns. Database optimization would fall into the very boring subject of normalization xD I agree the yes/no/true/false columns should be changed to boolean (int if database doesn't support it or even enum('true', 'false')). And the gender also, that one deserves to be a char

But I'm not really into databases, normalizing them and stuff has never been my thing, just talking out of personal opinion

---

Having Mysidia on git would really help. People who like this framework could contribute with bug fixes and aditional features, etc. SourceTree is a good interface that supports git, so you don't need to use the command line if you feel uncomfortable with it