Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

20 tips for MySQL db architects

scribd.com

Post reply on HN
Print view View original

11–20 of 27 posts

Re: 20 tips for MySQL db architects

#11

Post by jlouis »

tocomment wrote: post #5

Why does he say varchar(255) is bad design?

If the MySQL documentation is right, it doesn't matter at all in MySQL. VARCHARs up to 255 uses an extra byte for counting the size of the column. There is a reading problem with variable-size records of any kind compared to fixed-size records.

It goes to say that other databases uses different allocation strategies for VARCHARs so the advice can't be carried over. For instance, they might define a fixed-size area together with an optional overflow area. This way one can make it such that most records have a fixed size and gain speedup.

Re: 20 tips for MySQL db architects

#12

Post by axod »

ronaldbradford wrote: post #6
axod wrote: post #2

Obligatory complaint about how scribd takes 5 minutes to load and ends up as a worse user experience than linking to the pdf. Wait 5 minutes, then click on 'download'. sigh

axod, you can download these slides and other MySQL related presentations directly from http://ronaldbradford.com/presentations/

Very cool. Thanks I'll take a look there :)

Re: 20 tips for MySQL db architects

#13

Post by ars »

Small note about using unsigned ints: In PHP (and possibly others) they will turn into signed ints when you read them.

If you read and write them and never show them, it's probably OK, but if you have logic that checks for But then again, if you have so many rows in a table that your auto_number has reached over 2 billion, you probably have other issues as well.

Re: 20 tips for MySQL db architects

#15

Post by ars »

petercooper wrote: post #8
tocomment wrote: post #5

Why does he say varchar(255) is bad design?

Because 255 is now just an arbitrary choice for a VARCHAR length. Explanation: Prior to MySQL 5.0.3 (give or take a few point releases - I forget) a VARCHAR column could be 255 characters in length maximum, so VARCHAR(255) was often used as a default. Now, however, you can go up to 65,535 characters on VARCHAR, so if you're still using "255" then that seems arbitrary and not well thought out (or your schema is just o…

That's not totally true. Using a varchar of 255 or less saves one byte vs one over 255. So if you don't know any particular size to use, 255 is reasonable.

And varchar(20) vs varchar(255) for a 10 character string use _exactly_ the same space. So there is no reason to artificially use a smaller value.

Re: 20 tips for MySQL db architects

#16

Post by bayareaguy »

ars wrote: post #15
petercooper wrote: post #8

Earlier quoted context omitted.

Because 255 is now just an arbitrary choice for a VARCHAR length. Explanation: Prior to MySQL 5.0.3 (give or take a few point releases - I forget) a VARCHAR column could be 255 characters in length maximum, so VARCHAR(255) was often used as a default. Now, however, you can go up to 65,535 characters on VARCHAR, so if you're still using "255" then that seems arbitrary and not well thought out (or your schema is just o…

That's not totally true. Using a varchar of 255 or less saves one byte vs one over 255. So if you don't know any particular size to use, 255 is reasonable. And varchar(20) vs varchar(255) for a 10 character string use _exactly_ the same space. So there is no reason to artificially use a smaller value.

It depends on what you mean by artificial. It's a good idea to use a smaller value if you have an idea of what the actual limit should be for you want the column to contain.

Re: 20 tips for MySQL db architects

#17

Post by petercooper »

ars wrote: post #15
petercooper wrote: post #8

Earlier quoted context omitted.

Because 255 is now just an arbitrary choice for a VARCHAR length. Explanation: Prior to MySQL 5.0.3 (give or take a few point releases - I forget) a VARCHAR column could be 255 characters in length maximum, so VARCHAR(255) was often used as a default. Now, however, you can go up to 65,535 characters on VARCHAR, so if you're still using "255" then that seems arbitrary and not well thought out (or your schema is just o…

That's not totally true. Using a varchar of 255 or less saves one byte vs one over 255. So if you don't know any particular size to use, 255 is reasonable. And varchar(20) vs varchar(255) for a 10 character string use _exactly_ the same space. So there is no reason to artificially use a smaller value.

> 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 :)

Re: 20 tips for MySQL db architects

#18

Post by mkn »

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 nuanced optimization are just pointless; The compiler saw you coming and optimized your code behind your back before you got there. Why can't storage engines be like this? Why do you have to think not only about the type of your data, but how you want it implemented as well? Are there technical reasons why a db can't take generic data declarations ('string' instead of 'varchar(255)') and do the right thing with it when you populate your db? Or treat the use of a transaction as a hint that you'd like a storage engine that supports transactions?

I realize there comes a point where the db design has to be clamped down for production, but in the design stages there's a lot of optimization that a 'SQL compiler' could do behind your back. It currently seems like database product designers have taken the lazy way out and decided that optimization should live in the developers' heads rather than in the engine.

It just seems like SQL is this awful holdover from the days of COBOL and that we seriously need a modern product that let's us think about our programming problems rather than SQL's hangups. Am I missing something really basic, here?

Let me emphasize that, afaik, ronaldbradford knows his stuff, and I'm glad for him if he can make a living off of his MySQL knowledge. I just wish that that knowledge was embedded in the products themselves.

Re: 20 tips for MySQL db architects

#19

Post by newt0311 »

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…

s/SQL/MySQL

Other dbs like oracle, db2, postgres, and sql server are indeed much better about this. Ie. they only require optimizations on statistics like resident memory, etc... The actual queries do not need to be optimized except in extreme cases.

Re: 20 tips for MySQL db architects

#20

Post by ronaldbradford »

petercooper wrote: post #17
ars wrote: post #15

Earlier quoted context omitted.

That's not totally true. Using a varchar of 255 or less saves one byte vs one over 255. So if you don't know any particular size to use, 255 is reasonable. And varchar(20) vs varchar(255) for a 10 character string use _exactly_ the same space. So there is no reason to artificially use a smaller value.

> 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 very important, because MySQL unlike Oracle for example, does not cap the amount of memory used for the Process, again using the Oracle approach the PGA (Process Global Area).

It's always easy to take sort cuts in design, when your DB will never grow above a few GB's and your users will never grow above say 10.

VARCHAR(255) is just as much a sign of little to no design, and therefore this and many other beginner 101 mistakes are likely, and therefore large performance gains are generally easily made in a system that now is needed to perform.

Post reply on HN