YOUR WEBSITE

            There are many clauses, and combinations thereof, that can be used with a SELECT statement to yield  particular table query results.  They range from very basic, commonly-used options to very specialized and  complex.  The following basic, optional clauses will be covered in this section:  Clauses                   Definition DISTINCT                Eliminates duplicate row data  FROM                      Specifies what table(s) to retrieve data from  WHERE                   Decides what data to show  ORDER BY             Sorts data by specified order criteria  LIMIT                        Reduces the amount of records received  Example of SELECT statement with clauses:  SELECT DISTINCT values_to_display FROM table_name WHERE expression ORDER BY how_to_sort LIMIT row_count;               The above syntax shows the correct order and usage of each of the above optional clauses.  This statement is specifically  selecting  distinct data rows  from a named ta

The  SELECT statement is primarily used to retrieve zero or more  rows from one or more tables in a  database.  In MySQL,  SELECT is the most commonly used DML (Data Manipulation Language)  command.  In specifying a SELECT query, the user specifies a description of the desired result set.  It is  built with optional clauses that specify how and what data to retrieve.  The general command syntax is  shown below;  SELECT [<clause options>] <column list> [FROM] <table>  [<clause options>];  Basic SELECT statement example using world database (and the result data):  mysql> SELECT Name FROM Country;  +-------------------------------------------------+  | Name                                                    |  +-------------------------------------------------+  | Afghanistan                                          |  | Netherlands                                          |  | Netherlands Antilles                            |  | Albania        

The FOREIGN KEY clause has several parts:   •  It names the column in the referring table (country_code).   •  It names the code column in the country_parent table as the referenced column. This column is  the “foreign” key.   •  It specifies what actions to take if records are modified in the referenced table. The foreign key  definition shown specifies the  CASCADE action for both  UPDATE and  DELETE operations. This  means that changes in the parent table are cascaded down to the child table. If a  code value is  changed in the  country_parent table, InnoDB changes any corresponding  city_child  records with that value in the country_code column to match. If a country_parent record is  deleted, InnoDB also deletes any  city_child records with the same country code. (InnoDB  supports actions other than CASCADE, but they are not covered here.   Instead of  CASCADE a SET NULL or RESTRICT can be specified.  In a foreign key relationship, the referring column and the

A foreign key relationship allows for the declaration of an index in one table to be related to an index in  another.  It also allows for the placing of constraints on what may be done to the tables in the relationship.   The database enforces the rules of this relationship to maintain referential integrity.  Using Foreign Keys with InnoDB  The InnoDB storage engine supports the use of foreign keys (unlike other storage engines).  This capability  enables the declaration of relationships between columns in different tables, and InnoDB maintains  integrity between the tables by prohibiting operations that violate those relationships.  For example, the  following restrictions must be addressed:   •  A table must contain only records with ID values that are known in another reference table.   •  If an ID in the referenced table is changed, the ID in all matching records in the referencing table  must be changed to match.   •  If a record with a given ID in the referenced t

To view the status of indexes in a table use the following statement;  SHOW INDEX FROM <table>;  Example:  mysql> SHOW INDEX FROM countrylanguage\G  *************************** 1. row ***************************         Table: countrylanguage  Non_unique: 0     Key_name: PRIMARY  Seq_in_index: 1  Column_name: CountryCode     Collation: A  Cardinality: NULL     Sub_part: NULL       Packed: NULL         Null:  Index_type: BTREE       Comment:  *************************** 2. row ***************************         Table: countrylanguage  Non_unique: 0     Key_name: PRIMARY  Seq_in_index: 2  Column_name: Language     Collation: A  Cardinality: 984     Sub_part: NULL       Packed: NULL         Null:  Index_type: BTREE       Comment:  2 rows in set (0.16 sec)  The result of the  SHOW INDEX command done on the  CountryLanguage table lists two indexes;  CountryCode and Language.  This confirms the setting of a composite index in the CREATE TABLE  c

•  Declare a NOT NULL indexed column if possible.   NULL requires additional server decisions   and can be processed more simply and faster.  •  Avoid over indexing; don't index a column just because it can be indexed. If a column is never  going to be referred to in comparisons, then there's no need to index it.  •  Every index created slows down table updates.  If a row is inserted, an entry must be added to  each of the table's indexes.  •  An index on a column that has very few distinct values is unlikely to do much good.  •  Choose unique and non-unique indexes appropriately.  The choice may be influenced by the data  type of a column and the level of distinctness.  •  Index a column prefix rather than the entire column. Shortening the length of the key values can  improve performance by reducing the amount of disk I/O needed.  •  Avoid creating multiple indexes that overlap (have the same initial columns). 

When a row is inserted into a table, the database server does not attempt to put the data in any particular  location within the table.  Instead, the server simply places the data in the next available location within the  file.  When a query is sent against the table therefore, the server will need to inspect every row of the table  to answer the query.  Tables in MySQL can grow very large, and as a table gets bigger, retrievals from it  become slower.  An index in MySQL serves to assist in finding row data more quickly and easily, much like an index in the  back of a technical manual.  Database indexes are used to locate rows in a table.  Indexes are special  structures that, unlike normal tables, are kept in a specific order.  Instead of containing all of the data about  an entity, however, an index contains only the column(s) used to locate rows in the data table.  It also  contains information describing where the rows are physically located.  Usually, all the indexes requi