| The MySQL database application supports several different types of tables (a table's type is also called its storage engine). Although each type supports a different feature set, how you interact with themin terms of running queriesis generally consistent.  Tips
Indexes are less efficient on variable-length columns, just as MySQL is generally slower dealing with fields that are not of a fixed length. Indexes can be named when they are created: ALTER TABLE tablename ADD INDEX  columnname)
If they are not, they will take the name of the column to which they are applied. You can also add indexes to a table using the CREATE INDEX command. I prefer ALTER, though, as it's used for other table modifications (in other words, you'll probably need to know ALTER regardless). Because an ALTER command could have serious repercussions on a table, you should always back up the table before execution. The word COLUMN in most ALTER statements is optional. When adding a new column to a table, you can use the AFTER columname description to indicate where in the table the new column should be placed. ALTER TABLE clients ADD COLUMN  last_name VARCHAR(25) AFTER first_name
Indexes can have set lengths, so that only a certain number of characters in a column are indexed. The following index will track only the first five characters in the last_name column: ALTER TABLE clients ADD INDEX start_name (last_name(5))
At the end of the chapter, the EXPLAIN command will be used to indicate when and what indexes are involved a query.
The most important table type is MyISAM. MyISAM tables are great for most applications, handling SELECTs and INSERTs very quickly. But the MyISAM storage engine cannot manage transactions, a feature I talk about in Chapter 11. After MyISAM, the two most popular storage engines are InnoDB and MEMORY (which used to be called HEAP). InnoDB tables have been part of the default MySQL installation since version 4.0 (if you are using an earlier version, you must enable InnoDB support; see the MySQL manual). InnoDB tables can be used for transactions and perform UPDATEs nicely. But the InnoDB storage engine is generally slower than MyISAM and requires more disk space on the server. The MEMORY table type is the best performer of the bunch, as such tables store all data in memory, not in files. This comes at a price, as MEMORY tables can only deal with fixed-length column types, have no support for AUTO_INCREMENT, and lose all data in a crash. To specify the storage engine when you define a table, add a clause to the end of the creation statement: CREATE TABLE tablename ( column1name COLUMNTYPE, column1name COLUMNTYPE... ) ENGINE = INNODB
(Prior to MySQL 4.0.18, you have to use the word TYPE instead of ENGINE). If you don't specify a storage engine when creating tables, MySQL will use the default type. This is normally MyISAM, but it is InnoDB if you installed MySQL on Windows using the MySQL Configuration Wizard. To change the type of an existing tablewhich is perfectly acceptableuse an ALTER command: ALTER TABLE tablename ENGINE = MYISAM
Because the next example in this chapter will require a MyISAM table, I'll run through the steps necessary for setting the storage engine on the urls table. The first couple of steps will show you how to see the current storage engine being used (as you may not need to change the urls table's type). To change a table's type 1. | Log into the mysql client and select the contents database.
| 2. | View the current table information (Figure 5.30).
SHOW TABLE STATUS;
Figure 5.30. Before altering a table's type, I view its current type with the SHOW TABLES STATUS command. 
The SHOW TABLE STATUS command returns all sorts of useful information about your database's tables. The returned result will be hard to read, though, as it is a wide table displayed over multiple lines. What you're looking for is this: The first item on each row is the table's name, and the second item is the table's engine, or table type. The engine will most likely be either MyISAM or InnoDB.
| 3. | Change the urls table to MyISAM, if necessary (Figure 5.31).
ALTER TABLE urls ENGINE=MYISAM;
Figure 5.31. I've changed the table's type (or storage engine) using an ALTER command. 
If the results in Step 2 (Figure 5.30) indicate that the engine is anything other than MyISAM, you'll need to change it over to MyISAM using this command (capitalization doesn't matter). With the default MySQL installation and configuration, changing the table's type wasn't necessary on Mac OS X but was on Windows.
| 4. | If desired, confirm the engine change by rerunning the SHOW TABLE STATUS command.
|
Tips To make any query's results easier to view in the mysql client, you can add the \G parameter (Figure 5.32): SHOW TABLE STATUS \G
Figure 5.32. For a more legible version of the query results, I've added the \G option in the mysql client. 
This flag states that the table of results should be displayed vertically instead of horizontally. Notice that you don't need to use a terminating semicolon now, because the \G ends the command. The same database can have tables of different types. This may be true for your contents database now (depending upon your default table type). You may also see this with an e-commerce database that uses MyISAM for customers and products but InnoDB for orders (to allow for transactions). The MyISAM table type uses upward of three files for each table. There will be a tablename.frm file that has the table's definition, a tablename.myd that stores the actual data, and a tablename.myi that stores the indexes. There are a number of configuration options for the InnoDB storage engine. See the MySQL manual for specifics.
|
