| Indexes are a special system that databases use to improve the overall performance. By setting indexes on your tables, you are telling MySQL to pay particular attention to that column (loosely said). In fact, MySQL creates extra files to store and track indexes efficiently. MySQL allows for at least 16 indexes for each table, and each index can incorporate up to 15 columns. While a multicolumn index may not seem obvious, it will come in handy for searches frequently performed on the same combinations of columns (e.g., first and last name, city and state, etc.). On the other hand, you should not go overboard with indexing. While it does improve the speed of reading from databases, it slows down the process of altering data in a database (because the changes need to be recorded in the index). Indexes are best used on columns Altering Tables The ALTER SQL term is primarily used to modify the structure of a table in your database. Commonly this means adding, deleting, or changing the columns therein, but it also includes the addition of indexes. An ALTER statement can even be used for renaming the table as a whole. While proper database design should give you the structure you need, in the real world, making alterations is commonplace. The basic syntax of ALTER is ALTER TABLE tablename CLAUSE
Because there are so many possible clauses, I've listed the common ones in Table 5.8. If you need to rename a table, you can alternatively use the RENAME TABLE syntax: RENAME TABLE old_name TO new_name
Table 5.8. Common variants on the ALTER command. See the MySQL manual for the full specifications.ALTER TABLE Clauses |
|---|
CLAUSE | USAGE | MEANING |
|---|
ADD COLUMN | ALTER TABLE tablename ADD COLUMN column_name VARCHAR(40) | Adds a new column to the end of the table. | CHANGE COLUMN | ALTER TABLE tablename CHANGE COLUMN column_name column_name VARCHAR(60) | Allows you to change the data type and properties of a column. | DROP COLUMN | ALTER TABLE tablename DROP COLUMN column_name | Removes a column from a table, including all of its data. | ADD INDEX | ALTER TABLE tablename ADD INDEX indexname (column_name) | Adds a new index on column_name. | DROP INDEX | ALTER TABLE tablename DROP INDEX indexname | Removes an existing index. | RENAME AS | ALTER TABLE tablename RENAME AS new_tablename | Changes the name of a table. |
|
MySQL has four types of indexes: INDEX (the standard), UNIQUE (which requires each row to have a unique value for that column), FULLTEXT (for performing FULLTEXT searches), and PRIMARY KEY (which is just a particular UNIQUE index and one you've already been using). With this in mind, I'll modify my tables by adding indexes to them. To do so, I'll use the ALTER command, as described in the sidebar. To add an index to an existing table 1. | Add an index on the category column in the url_categories table (Figure 5.26).
ALTER TABLE url_categories ADD  UNIQUE(category);
Figure 5.26. A unique index is placed on the category column. This will improve the efficiency of certain queries and protect against redundant entries. 
The url_categories table already has a primary key index on the url_category_id. Since the category may also be a frequently referenced field and since its value should be unique across every row, I add a UNIQUE index to the table.
| 2. | Add an UNIQUE index on the url column in the urls table (Figure 5.27).
ALTER TABLE urls ADD UNIQUE(url);
Figure 5.27. An index has been added to the urls table. MySQL will report on the success of a query and how many rows were affected when using ALTER statements. 
Similar to the url_categories table, the urls table already has a primary key index on the url_id. The url may also be a frequently referenced fieldas you've seenand since its value should be unique across every row, I add a UNIQUE index to the table. Neither the title nor description fields are used in such a way that they would benefit from indexes.
| 3. | Add indexes to the url_associations table (Figure 5.28).
ALTER TABLE url_associations ADD INDEX (url_id), ADD INDEX (url_category_id), ADD INDEX (date_submitted);
Figure 5.28. With SQL, I am allowed to add multiple indexes at once. 
Finally, I'll add three indexes to the url_associations table. First I'll want to add nonunique indexes to the foreign keys, url_id and url_category_id, since these are used in WHERE conditionals and joins. Then I'll also index the submission date, which will come up in my conditionals. The table's ua_id field is automatically indexed as a primary key, and the approved column shouldn't be indexed, since its value will never be anything but Y or N.
| 4. | View the current structure of each table (Figure 5.29).
DESCRIBE url_categories; DESCRIBE urls; DESCRIBE url_associations;
Figure 5.29. To view the details of a table's structure, use DESCRIBE. 
The DESCRIBE SQL term will tell you information about a table's column names and order, column types, and index types (under Key). It also indicates whether or not a field can be NULL, what default value has been set (if any), and more.
|
|
