View Single Post
  #115  
Old 12-19-2014, 01:31 AM
Kyttias's Avatar
Kyttias Kyttias is offline
Premium Member
 
Join Date: Jan 2014
Posts: 857
Gender: Unknown/Other
Credits: 88,017
Kyttias is on a distinguished road
Default

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... =/
__________________
Please do not contact me directly outside of Mysidia.
I also cannot troubleshoot code more than two years old - I legit don't remember it.

Last edited by Kyttias; 12-19-2014 at 01:57 AM.
Reply With Quote