20 tips for MySQL db architects

Post by gaius » Tue, Oct 21, 2008, 2:05 PM UTC

mkn wrote: post #18

The meta-point for me is that SQL is just broken, as are its many implementations. We'd never tolerate a scripting language that made you learn all the implementation details before you were effective with it. And yet we tolerate this from database engines and the languages we use to interact with them. The opposite end of the spectrum is modern compilers. C/C++ compilers are generally so good that most attempts at n…

Post by mkn » Tue, Oct 21, 2008, 5:29 PM UTC

gaius wrote: post #21

mkn wrote: post #18

The meta-point for me is that SQL is just broken, as are its many implementations. We'd never tolerate a scripting language that made you learn all the implementation details before you were effective with it. And yet we tolerate this from database engines and the languages we use to interact with them. The opposite end of the spectrum is modern compilers. C/C++ compilers are generally so good that most attempts at n…

I just wish that that knowledge was embedded in the products themselves Err, it is. Oracle, Sybase, MS SQL, Informix, all the major database products have sophisticated query optimizers. You use SQL to describe the result set you want, and it figures out the best way to get it. Sadly the Web 2.0 world is full of people who actually believe MySQL is as good as databases get, having never used anything else. It's proba…

Post by gaius » Tue, Oct 21, 2008, 6:41 PM UTC

ronaldbradford wrote: post #20

petercooper wrote: post #17

Earlier quoted context omitted.

> So there is no reason to artificially use a smaller value. That's not true either. As the document explains, MySQL's internal buffering uses fixed sized columns matching the length limit of the VARCHAR column. So a VARCHAR(255) will cause 255 bytes of memory to be allocated versus only 20 for VARCHAR(20). Of course, it depends on what range of sizes your data fit as to what you do :)

This is also multiplied by the character encoding, so when you have a table with 10 columns at VARCHAR(255) because your too lazy to define them, and they are all UTF8, so that's 255*3 for each column for each row when used in a MySQL Internal buffer (not, these buffers may or may not be used depending on the type of query). When you have 100s to 1000s of queries per second, knowing and using your memory wisely is ve…

Post by newt0311 » Tue, Oct 21, 2008, 6:53 PM UTC

mkn wrote: post #23

gaius wrote: post #21

Earlier quoted context omitted.

I just wish that that knowledge was embedded in the products themselves Err, it is. Oracle, Sybase, MS SQL, Informix, all the major database products have sophisticated query optimizers. You use SQL to describe the result set you want, and it figures out the best way to get it. Sadly the Web 2.0 world is full of people who actually believe MySQL is as good as databases get, having never used anything else. It's proba…

I hear the points about query optimizers that you and the other repliers have made. (And they are valid points, all. I was just misinformed about MySQL.) I was more addressing the points made about database design decisions w/respect to data type choice and their effect on storage requirements and speed. I was wondering, specifically, if it is technically feasible to allow designers to specify 'text' or 'string' at d…