Capita Plc Most Frequently Asked Latest MySQL Interview Questions Answers
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.
What Is Index?
An index is a single column or multiple columns defined to have values pre-sorted to speed up data retrieval speed.
What Is Join?
Join is data retrieval operation that combines rows from multiple tables under certain matching conditions to form a single row.
What Is Union?
Join is data retrieval operation that combines multiple query outputs of the same structure into a single output. By default the MySQL UNION removes all duplicate rows from the result set even if you don’t explicit using DISTINCT after the keyword UNION.
SELECT customerNumber id, contactLastname name
FROM customers
UNION
SELECT employeeNurrber id, firstname name
FROM employees
id name
103 Schmitt
112 King
114 Ferguson
119 Labrune
121 Bergulfsen
What Is Isam?
ISAM (Indexed Sequential Access Method) was developed by IBM to store and retrieve data on secondary storage systems like tapes.
What Is Innodb?
lnnoDB is a transaction safe storage engine developed by Innobase Oy (an Oracle company now).
What Is Bdb (berkeleydb)?
BDB (BerkeleyDB) is transaction safe storage engine originally developed at U.C. Berkeley. It is now developed by Sleepycat Software, Inc. (an Oracle company now).
What Is Csv?
CSV (Comma Separated Values) is a file format used to store database table contents, where one table row is stored as one line in the file, and each data field is separated with comma.
What Is Transaction?
A transaction is a logical unit of work requested by a user to be applied to the database objects. MySQL server introduces the transaction concept to allow users to group one or more SQL statements into a single transaction, so that the effects of all the SQL statements in a transaction can be either all committed (applied to the database) or all rolled back (undone from the database).
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.
 |
| Capita Plc Most Frequently Asked Latest MySQL Interview Questions Answers |
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.
What Is Index?
An index is a single column or multiple columns defined to have values pre-sorted to speed up data retrieval speed.
What Is Join?
Join is data retrieval operation that combines rows from multiple tables under certain matching conditions to form a single row.
What Is Union?
Join is data retrieval operation that combines multiple query outputs of the same structure into a single output. By default the MySQL UNION removes all duplicate rows from the result set even if you don’t explicit using DISTINCT after the keyword UNION.
SELECT customerNumber id, contactLastname name
FROM customers
UNION
SELECT employeeNurrber id, firstname name
FROM employees
id name
103 Schmitt
112 King
114 Ferguson
119 Labrune
121 Bergulfsen
What Is Isam?
ISAM (Indexed Sequential Access Method) was developed by IBM to store and retrieve data on secondary storage systems like tapes.
What Is Innodb?
lnnoDB is a transaction safe storage engine developed by Innobase Oy (an Oracle company now).
What Is Bdb (berkeleydb)?
BDB (BerkeleyDB) is transaction safe storage engine originally developed at U.C. Berkeley. It is now developed by Sleepycat Software, Inc. (an Oracle company now).
What Is Csv?
CSV (Comma Separated Values) is a file format used to store database table contents, where one table row is stored as one line in the file, and each data field is separated with comma.
What Is Transaction?
A transaction is a logical unit of work requested by a user to be applied to the database objects. MySQL server introduces the transaction concept to allow users to group one or more SQL statements into a single transaction, so that the effects of all the SQL statements in a transaction can be either all committed (applied to the database) or all rolled back (undone from the database).