Value:		'**' around word(s) to underline.
Links:		Put one link on each line
More Info:		Put items on seperate lines
Tags:		Seperate tags by commas
Ranking:

Fillin (hover over or click) the blanks to learn about MySQL, or create your own lines

+ To find structure of a MySQL table use the 'describe table_name' command

+ To move a column in MySQL: ALTER TABLE `tablename` MODIFY column_to_move column_to_move_type AFTER column_to_place_after

+ indexes cause insert operations take a longer time

+ GROUP_CONCAT() function is used to concatenate column values into a single string.

+ Ordinary conditional statements can be used inside ordinary queries? true

+ To avoid the overhead of sorting that GROUP BY produces, add ORDER BY NULL

+ To return a string result with the concatenated non-NULL values from a group use the GROUP_CONCAT MySQL function.

+ The 'cardinality' of a table field is the number of unique values for that field

+ Keep database connection file outside of the web root

+ mysql_free_result() only needs to be called if you are concerned about how much memory is being used for queries that return large result sets.

+ MySQL 5 brought stored procedures, triggers, and views.

+ For concurrency control, MyISAM uses table-level locking with concurrent inserts.

There are, in fact, three types of locks in MyISAM:
READ LOCAL lock. This is used by queries that only need to read. This type of lock merely blocks updates, to prevent the data from changing during the query. Other read queries can proceed, as can concurrent inserts: if an INSERT statement can add the new data at the end of the .MYD file, rather than by filling existing free space (from rows that were deleted), it does not have to wait on a READ LOCAL lock.
READ, or shared locks. This locking type blocks updates, including all INSERTs. It is generally used if an external tool such as myisamcheck needs to access a table file directly.
WRITE, or exclusive locks. This is used by DELETE, UPDATE and sometimes INSERT statements. All other access (readers as well as writers) to the table is blocked, to prevent updates from interfering with each other.
By "blocking" we mean that a thread (connection) may have to wait until another thread has completed its operation and releases the lock. While is is possible to set locks manually, the MySQL server employs the necessary locking implicitly for any statement it executes.
MyISAM does not support transactions. Effectively, this storage format always operates in "AUTOCOMMIT=1" mode where each statement is atomic. Please note that special constructs such as "INSERT ... SELECT" and "CREATE TABLE ... SELECT" are regarded as single statements. The isolation level is fixed, but would be equivalent to "READ COMMITTED".
MyISAM index blocks are cached globally in the key buffer (i.e. all threads share that cache), for table data MyISAM relies on the caching of the operating system.
With its simple architecture, MyISAM offers high performance with low overhead in terms of memory and disk utilization.
Backups can be made using either mysqldump (SQL scripts) or mysqlhotcopy (binary copy of the table files), both of which require shared locks on the tables being backed up. In case of relationships between tables, additional shared locking may be required to ensure consistency of the backup.
In case of trouble, fixed-row format tables are easiest to recover, as the recovery tools know exactly how long each record is and where they can start. In the case of a system failure, generally only the indexes are affected. These can be easily rebuilt (even automatically), though this can take considerable time for larger tables.

http://dev.mysql.com/tech-resources/articles/storage-engine/part_2.html

MySQL

✯ ✯ ✯ ✯ ✯

+ In MySQL, if transaction support is needed, use InnoDB, but if full text searching is needed use MyISAM

+ Extremely high concurrency is possible when using InnoDB, with the following trade-off: InnoDB requires about three times as much disk space compared to MyISAM, and for optimal performance, lots of RAM is required for the InnoDB buffer pool.

+ The MySQL SELECT INTO statement selects data from one table and inserts it into a different table.

+ Date arithmetic can be performed using INTERVAL together with the + or - operator

+ If MySQL autocommit mode is disabled, the session always has a transaction open.

+ The major deficiency of MyISAM is the absence of transactions support

+ To perform mathematical operations on null values use coalesce(null, 0)

+ MySQL only supports the EXPLAIN statement for SELECT queries

+ SQL injection is when user input causes the execution of unwanted SQL on the database

+ A TIMESTAMP column is simply a DATETIME column that automatically updates to the current time every time the contents of that record are altered.

+ Normalization can affect performance negatively

+ The CONCAT_WS MySQL function concatenates with seperator and avoids NULL values

+ A natural join is a special case of the equi-join

+ Consistency means that when a transaction commits or aborts, the database is always left in a consistent state.

+ ACID (Atomicity, Consistency, Isolation, Durability) guarantees database transactions are processed reliably

+ To show the sql statements necessary to duplicate rights for another user use SHOW GRANTS [FOR user]

+ Durability guarantees a pending database changes are tracked so that the server can recover from an abnormal termination.

+ Atomicity means "all or nothing"

+ Isolation keeps transactions separated from each other until they're finished.

+ Consistency guarantees that a transaction never leaves the database in a semi finished state.