Alex Stetsenko

There is a session variable FOREIGN_KEY_CHECKS which allows to ignore checking of foreign key constraints for InnoDB tables.

If set to 1 (the default), foreign constraints are checked. If set to 0, they are ignored.
Setting FOREIGN_KEY_CHECKS to 0 also affects data definition statements like DROP TABLE which drops tables that have foreign keys that are referred to by other tables.

Disabling foreign key checking can be useful for reloading InnoDB tables in an order different from that required by their parent/child relationships.

Using session variables:

SET FOREIGN_KEY_CHECKS = 0;
 
SELECT @@FOREIGN_KEY_CHECKS;
 
SET FOREIGN_KEY_CHECKS = 1;

There is a simple way to find out current storage engine for any table(s) using SHOW TABLE STATUS query.

SHOW TABLE STATUS LIKE `User`;

Column Engine in result of query above shows current storage engine for table `User`.

SHOW TABLE STATUS LIKE `User%`;

Column Engine in result of query above (separate row for each table) shows current storage engine for tables like `User`, `UserPhoto`, `UserCommet` etc.

SHOW TABLE STATUS FROM `TestDB`;

Query above allows to find out storage engine for each table in database.

SHOW TABLE STATUS WHERE `Engine` = 'InnoDB';

And this query allows to get list of tables with storage engine InnoDB.

SQL query below helps to change table storage engine:

ALTER TABLE `User` ENGINE = MyISAM;

How can you select the minimum value in a row of data? This is quite tricky question for MS SQL but not for MySQL.
There are functions LEAST() and GREATEST() to get minimum and maximum value in a row.

For example a SQL query like below returns minimum of those three dates.

SELECT LEAST(DateCreated, DateChanged, DateUploaded)
FROM UserPhoto;

The same is for maximum:

SELECT GREATEST(DateCreated, DateChanged, DateUploaded)
FROM UserPhoto;

Draw attention that LEAST() (or GREATEST()) returns NULL if at least one input parameter is NULL. Thus one of possible workarounds is using of function IFNULL().for each input parameter.

A temporary table could be very useful in some cases to keep temporary data.

There are couple interesting things about temporary tables.

1) A temporary table is visible only to the current connection, and is dropped automatically when the connection is closed.This means that two different connections can use the same temporary table name without conflicting with each other.

2) There is a short way to create a temporary table filled with data:

CREATE TEMPORARY TABLE MyTemporaryTable
SELECT ID, `Name` FROM MyRealTable WHERE ID < 10;

instead of more traditional way like:

CREATE TEMPORARY TABLE MyTemporaryTable
(
  ID INT,
  `Name` VARCHAR(100)
);
 
INSERT INTO MyTemporaryTable (ID, `Name`)
SELECT ID, `Name` FROM MyRealTable WHERE ID < 10;

Unfortunately MySQL 5 (at least 5.0.15) does not allow using variables or procedure parameters with LIMIT.

Here is a workaround where SQL statement is generated inside stored procedure:

CREATE PROCEDURE GetList (limitStart INT, limitCount INT)
BEGIN
 
SET @SQL = CONCAT('SELECT ID, Name FROM `User`
LIMIT ', limitStart, ', ', limitCount);
 
PREPARE query FROM @SQL;
EXECURE query;
DEALLOCATE PREPARE query;
 
END;