Making the Right Design Choices


There are various design choices you can make that will help to speed up your database performance. They are as listed here:

  • Use the smallest type that data will fit in. For example, if you are storing numbers from 1 to 10, don't use INT ; use TINYINT instead. The smaller your rows and tables are, the quicker they will be to search. The smaller your data is, the more rows can be cached in memory.

  • Use fixed-length records where possible. If all rows in a table are the same length, it's faster for MySQL to access rows in the middle of the table. To get fixed-length rows, all the column types you use must be of fixed length. This means no VARCHAR , no TEXT , and no BLOB .

    If you need to store TEXT and BLOB , you might consider denormalizing your schema to break the TEXT or BLOB fields out into a separate table.

    If you are using only VARCHAR , you can consider replacing them all with CHAR . This is something of a trade-off because CHAR will occupy more space on disk, going against our first suggestion in this list.

  • Declare as many columns NOT NULL as possible. If your data logically requires NULL values, then obviously you should use them. However, note that you are paying a small speed and storage space price for these, so use NOT NULL wherever possible.

  • Choose the table type on a table-by-table basis. Non “transaction-safe tables (for example, MyISAM) involve a lot less overhead and are therefore faster than the transaction-safe types (InnoDB and BDB). MySQL allows you to have a mixture of table types in the one database. Choose the fastest one that can do each job. (You can find more information about the different types in Chapter 9, "Understanding MySQL's Table Types.")

  • Choose appropriate indexes. We will cover this topic in detail in the next section.

  • In extreme cases, you may even consider denormalization of tables to reduce the number of joins made for common queries. Because this can make your database a nightmare to maintain, it is not generally recommended.



MySQL Tutorial
MySQL Tutorial
ISBN: 0672325845
EAN: 2147483647
Year: 2003
Pages: 261

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net