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
SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE
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.
General Information About Mysql.
MySQL is a very fast, multi-threaded, multi-user, and robust SQL (Structured Query Language) database server.
Why Sql Is A Database Management System?
A database is a structured collection of data. It may be anything from a simple shopping list to a picture gallery or the vast amounts of information in a corporate network. To add, access, and process data stored in a computer database, you need a database management system such as MySQL. Since computers are very good at handling large amounts of data, database management plays a central role in computing, as stand-alone utilities, or as parts of other applications.
Why Use Mysql?
MySQL is very fast, reliable, and easy to use. If that is what you are looking for, you should give it a try. MySQL also has a very practical set of features developed in very close cooperation with our users. You can find a performance comparison of MySQL to some other database managers on our benchmark page. See section 12.7 Using Your Own Benchmarks. MySQL was originally developed to handle very large databases much faster than existing solutions and has been successfully used in highly demanding production environments for several years. Though under constant development, MySQL today offers a rich and very useful set of functions. The connectivity, speed, and security make MySQL highly suited for accessing databases on the Internet.
How Mysql Optimizes Distinct ?
DISTINCT is converted to a GROUP BY on all columns, DISTINCT combined with ORDER BY will in many cases also need a temporary table.
When combining LIMIT # with DISTINCT, MySQL will stop as soon as it finds # unique rows.
If you don't use columns from all used tables, MySQL will stop the scanning of the not used tables as soon as it has found the first match.
SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;
In the case, assuming t1 is used before t2 (check with EXPLAIN), then MySQL will stop reading from t2 (for that particular row in t1) when the first row in t2 is found.
How Mysql Optimizes Limit ?
In some cases MySQL will handle the query differently when you are using LIMIT # and not using HAVING:
If you are selecting only a few rows with LIMIT, MySQL will use indexes in some cases when it normally would prefer to do a full table scan.
If you use LIMIT # with ORDER BY, MySQL will end the sorting as soon as it has found the first # lines instead of sorting the whole table.
When combining LIMIT # with DISTINCT, MySQL will stop as soon as it finds # unique rows.
In some cases a GROUP BY can be resolved by reading the key in order (or do a sort on the key) and then calculate summaries until the key value changes. In this case LIMIT # will not calculate any unnecessary GROUP BY's.
As soon as MySQL has sent the first # rows to the client, it will abort the query.
LIMIT 0 will always quickly return an empty set. This is useful to check the query and to get the column types of the result columns.
The size of temporary tables uses the LIMIT # to calculate how much space is needed to resolve the query.
Mysql - Speed Of Delete Queries ?
If you want to delete all rows in the table, you should use TRUNCATE TABLE table_name. The time to delete a record is exactly proportional to the number of indexes. To delete records more quickly, you can increase the size of the index cache.
What Is The Difference Between Mysql_fetch_array And Mysql_fetch_object?
mysql_fetch_array — Fetch a result row as an associative ARRAY, a numeric array, or both
mysql_fetch_object — Fetch a result row as an OBJECT.
What Are The Different Table Present In Mysql?
MyISAM : This is default. Based on Indexed Sequntial Access Method. The above SQL will create a MyISA table.
ISAM : same
HEAP : Fast data access, but will loose data if there is a crash. Cannot have BLOB, TEXT & AUTO INCRIMENT fields
BDB : Supports Transactions using COMMIT & ROLLBACK. Slower that others.
InoDB : same as BDB
What Is Primary Key?
A primary key is a single column or multiple columns defined to have unique values that can be used as row identifications.
What Is Foreign Key?
A foreign key is a single column or multiple columns defined to have values that can be mapped to a primary key in another table.
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
SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE
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.
 |
| CGI Group MySQL Most Frequently Asked Latest Interview Questions Answers |
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.
General Information About Mysql.
MySQL is a very fast, multi-threaded, multi-user, and robust SQL (Structured Query Language) database server.
Why Sql Is A Database Management System?
A database is a structured collection of data. It may be anything from a simple shopping list to a picture gallery or the vast amounts of information in a corporate network. To add, access, and process data stored in a computer database, you need a database management system such as MySQL. Since computers are very good at handling large amounts of data, database management plays a central role in computing, as stand-alone utilities, or as parts of other applications.
Why Use Mysql?
MySQL is very fast, reliable, and easy to use. If that is what you are looking for, you should give it a try. MySQL also has a very practical set of features developed in very close cooperation with our users. You can find a performance comparison of MySQL to some other database managers on our benchmark page. See section 12.7 Using Your Own Benchmarks. MySQL was originally developed to handle very large databases much faster than existing solutions and has been successfully used in highly demanding production environments for several years. Though under constant development, MySQL today offers a rich and very useful set of functions. The connectivity, speed, and security make MySQL highly suited for accessing databases on the Internet.
How Mysql Optimizes Distinct ?
DISTINCT is converted to a GROUP BY on all columns, DISTINCT combined with ORDER BY will in many cases also need a temporary table.
When combining LIMIT # with DISTINCT, MySQL will stop as soon as it finds # unique rows.
If you don't use columns from all used tables, MySQL will stop the scanning of the not used tables as soon as it has found the first match.
SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;
In the case, assuming t1 is used before t2 (check with EXPLAIN), then MySQL will stop reading from t2 (for that particular row in t1) when the first row in t2 is found.
How Mysql Optimizes Limit ?
In some cases MySQL will handle the query differently when you are using LIMIT # and not using HAVING:
If you are selecting only a few rows with LIMIT, MySQL will use indexes in some cases when it normally would prefer to do a full table scan.
If you use LIMIT # with ORDER BY, MySQL will end the sorting as soon as it has found the first # lines instead of sorting the whole table.
When combining LIMIT # with DISTINCT, MySQL will stop as soon as it finds # unique rows.
In some cases a GROUP BY can be resolved by reading the key in order (or do a sort on the key) and then calculate summaries until the key value changes. In this case LIMIT # will not calculate any unnecessary GROUP BY's.
As soon as MySQL has sent the first # rows to the client, it will abort the query.
LIMIT 0 will always quickly return an empty set. This is useful to check the query and to get the column types of the result columns.
The size of temporary tables uses the LIMIT # to calculate how much space is needed to resolve the query.
Mysql - Speed Of Delete Queries ?
If you want to delete all rows in the table, you should use TRUNCATE TABLE table_name. The time to delete a record is exactly proportional to the number of indexes. To delete records more quickly, you can increase the size of the index cache.
What Is The Difference Between Mysql_fetch_array And Mysql_fetch_object?
mysql_fetch_array — Fetch a result row as an associative ARRAY, a numeric array, or both
mysql_fetch_object — Fetch a result row as an OBJECT.
What Are The Different Table Present In Mysql?
MyISAM : This is default. Based on Indexed Sequntial Access Method. The above SQL will create a MyISA table.
ISAM : same
HEAP : Fast data access, but will loose data if there is a crash. Cannot have BLOB, TEXT & AUTO INCRIMENT fields
BDB : Supports Transactions using COMMIT & ROLLBACK. Slower that others.
InoDB : same as BDB
What Is Primary Key?
A primary key is a single column or multiple columns defined to have unique values that can be used as row identifications.
What Is Foreign Key?
A foreign key is a single column or multiple columns defined to have values that can be mapped to a primary key in another table.