What are HEAP tables in MySQL?
HEAP tables are in-memory. They are usually used for high-speed temporary storage. No TEXT or BLOB fields are allowed within HEAP tables. You can only use the comparison operators = and <=>. HEAP tables do not support AUTO_INCREMENT. Indexes must be NOT NULL.
How do you control the max size of a HEAP table?
MySQL config variable max_heap_table_size.
What are CSV tables?
Those are the special tables, data for which is saved into comma-separated values files. They cannot be indexed.
Explain federated tables. ?
Introduced in MySQL 5.0, federated tables allow access to the tables located on other databases on other servers.
What is SERIAL data type in MySQL?
BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT
What happens when the column is set to AUTO INCREMENT and you reach the maximum value for that table?
It stops incrementing. It does not overflow to 0 to prevent data losses, but further inserts are going to produce an error, since the key has been used already.
Explain the difference between BOOL, TINYINT and BIT. ?
Prior to MySQL 5.0.3: those are all synonyms. After MySQL 5.0.3: BIT data type can store 8 bytes of data and should be used for binary data.
Explain the difference between FLOAT, DOUBLE and REAL. ?
FLOATs store floating point numbers with 8 place accuracy and take up 4 bytes. DOUBLEs store floating point numbers with 16 place accuracy and take up 8 bytes. REAL is a synonym of FLOAT for now.
If you specify the data type as DECIMAL (5,2), what’s the range of values that can go in this table?
999.99 to -99.99. Note that with the negative number the minus sign is considered one of the digits.
What happens if a table has one column defined as TIMESTAMP?
That field gets the current timestamp whenever the row gets altered.
But what if you really want to store the timestamp data, such as the publication date of the article?
Create two columns of type TIMESTAMP and use the second one for your real data.
Explain data type TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ?
The column exhibits the same behavior as a single timestamp column in a table with no other timestamp columns.
What does TIMESTAMP ON UPDATE CURRENT_TIMESTAMP data type do?
On initialization places a zero in that column, on future updates puts the current value of the timestamp in.
Explain TIMESTAMP DEFAULT ‘2006:09:02 17:38:44? ON UPDATE CURRENT_TIMESTAMP. ?
A default value is used on initialization, a current timestamp is inserted on update of the row.
If I created a column with data type VARCHAR(3), what would I expect to see in MySQL table?
CHAR(3), since MySQL automatically adjusted the data type.
MySQL – Stored Procedures and Triggers
A stored procedure is a set of SQL commands that can be compiled and stored in the server. Once this has been done, clients don’t need to keep reissuing the entire query but can refer to the stored procedure. This provides better performance because the query has to be parsed only once, and less information needs to be sent between the server and the client. You can also raise the conceptual level by having libraries of functions in the server.
A trigger is a stored procedure that is invoked when a particular event occurs. For example, you can install a stored procedure that is triggered each time a record is deleted from a transaction table and that automatically deletes the corresponding customer from a customer table when all his transactions are deleted.
The planned update language will be able to handle stored procedures, but without triggers. Triggers usually slow down everything, even queries for which they are not needed.
MySQL – Foreign Keys
Note that foreign keys in SQL are not used to join tables, but are used mostly for checking referential integrity (foreign key constraints). If you want to get results from multiple tables from a SELECT statement, you do this by joining tables:
SELECT * from table1,table2 where table1.id = table2.id;
The FOREIGN KEY syntax in MySQL exists only for compatibility with other SQL vendors’ CREATE TABLE commands; it doesn’t do anything. The FOREIGN KEY syntax without ON DELETE … is mostly used for documentation purposes. Some ODBC applications may use this to produce automatic WHERE clauses, but this is usually easy to override. FOREIGN KEY is sometimes used as a constraint check, but this check is unnecessary in practice if rows are inserted into the tables in the right order. MySQL only supports these clauses because some applications require them to exist (regardless of whether or not they work).
In MySQL, you can work around the problem of ON DELETE … not being implemented by adding the appropriate DELETE statement to an application when you delete records from a table that has a foreign key. In practice this is as quick (in some cases quicker) and much more portable than using foreign keys.
In the near future we will extend the FOREIGN KEY implementation so that at least the information will be saved in the table specification file and may be retrieved by mysqldump and ODBC. At a later stage we will implement the foreign key constraints for application that can’t easily be coded to avoid them.
MySQL – Reasons NOT to Use Foreign Keys constraints
There are so many problems with foreign key constraints that we don’t know where to start:
Foreign key constraints make life very complicated, because the foreign key definitions must be stored in a database and implementing them would destroy the whole “nice approach” of using files that can be moved, copied, and removed. The speed impact is terrible for INSERT and UPDATE statements, and in this case almost all FOREIGN KEY constraint checks are useless because you usually insert records in the right tables in the right order, anyway. There is also a need to hold locks on many more tables when updating one table, because the side effects can cascade through the entire database. It’s MUCH faster to delete records from one table first and subsequently delete them from the other tables.
You can no longer restore a table by doing a full delete from the table and then restoring all records (from a new source or from a backup).
If you use foreign key constraints you can’t dump and restore tables unless you do so in a very specific order. It’s very easy to do “allowed” circular definitions that make the tables impossible to re-create each table with a single create statement, even if the definition works and is usable.
It’s very easy to overlook FOREIGN KEY … ON DELETE rules when one codes an application. It’s not unusual that one loses a lot of important information just because a wrong or misused ON DELETE rule.
The only nice aspect of FOREIGN KEY is that it gives ODBC and some other client programs the ability to see how a table is connected and to use this to show connection diagrams and to help in building applicatons.
MySQL will soon store FOREIGN KEY definitions so that a client can ask for and receive an answer about how the original connection was made. The current `.frm’ file format does not have any place for it. At a later stage we will implement the foreign key constraints for application that can’t easily be coded to avoid them.
MySQL – `–‘ as the Start of a Comment
MySQL doesn’t support views, but this is on the TODO.