My opinion on MySql best practices, the new new 'chain letter' started by Ronald Bradford. As always, this is a developers point of view.
0. Make a database design. Databases generated automatically might work very well in development, but when you have large data volumes and lots of concurrent users they do not.
1. Be consistent in your choice of datatypes. I prefer to use just a subset in a system. Try to stick with SMALLINT UNSIGNED or INTEGER UNSIGNED for keys. Always use the same datatype for the same term in all tables, no joins with datatype conversion! Do not store numbers as CHAR/VARCHAR fields.
2. Make sure there are indexes supporting all searches that are part of routine system usage. If you have tables that are very small it might be better not to index them, but this should be an active decision.
3. Verify your database design with real volumes and tests covering the planned usage. A modern computer can quickly scan (without indexes) more data than you will enter by hand, so you will probably need a dump from a production system or generated data.
4.Use SQL_MODE ANSI and TRADITIONAL for new systems. For old ones, you might have to allow a less strict mode.
5. Choose your storage engine with care. Use InnoDB (and keep an eye on Falcon!!) if you need transactions, but even if you do, MYISAM migh be a good choice for some tables.
No comments:
Post a Comment