Learning Mysql

The Memory table type is useful when you want to force data to be in main memory and not on disk. You do this when you want very fast access to atypically small set of data. It’s ideal, for example, for storing and finding country names, lists of states or cities, or salutations. Don’t use it for large files, as you need main memory for other tasks, such as SQL query evaluation and whatever other tasks your computer performs.
Choose it when speed is essential for small tables with data that doesn’t change.
There are serious disadvantages to the Memory type that can make it annoying. The most serious is that when you stop and restart a MySQL server, the data stored in a
Memory table is lost. This means you need to restore it each time you start the MySQL
server, which you might do by using the
SOURCE
statement or by using the init-file option to cause it to load a file on startup the former is discussed in Running the
Monitor in Batch Mode in Chapter 3 and the latter in Resetting Forgotten MySQL
Passwords” in Chapter 9. This is also a good reason to ensure the data doesn’t change:
use it for tasks where you have a fixed set of choices, not for tasks where you’re dynamically updating those choices. Remember that if your MySQL server goes down,
you’ll lose any changes you’ve made if you’ve haven’t explicitly dumped them to a disk file.
The Memory type has one significant advantage it’s an extremely fast environment for searching for exact matches (for example, checking if a country entered by a user matches a list of valid countries. Its list of disadvantages and limitations is longer As discussed, data is lost when the server stops. You need to reload it each time the server starts It doesn’t support
TEXT
or
BLOB
type columns, or any of their variants Prior to MySQL 4.0.2, it doesn’t support indexes on columns that contain
NULL
values.
• Prior to MySQL 4.1.0, the
AUTO_INCREMENT
feature isn’t supported The tables are stored exclusively in memory. While this is what makes them fast,
it’s a disadvantage if there are many memory-based tables, if the memory-based tables are large, or if the server needs the memory for other tasks.
InnoDB
The InnoDB type is the heavyweight, reliable, high-performance choice for large-scale,
highly reliable applications. It’s similar to MyISAM but includes extra features that make it transaction-safe, reliable, and flexible for high-end applications. Choose it if you’re building an application that needs features MyISAM doesn’t have. In this book,
we don’t discuss those features in detail, so it’s unlikely you’ll need to use it while you’re learning MySQL. However, note that with MySQL 4.1.5 and later, InnoDB is the default table type in Windows when you download and install a binary package from the
MySQL AB website in practice, this has no impact on you, so don’t be too concerned whether the default is MyISAM or InnoDB while using this book.

Download 4.24 Mb.

Share with your friends: