«

»

Sep 16

MySQL Advance Questions and Answers

1. How many TRIGGERS are allowed in MySql table?

Ans.  MySql table allows following 6 triggers:

-BEFORE INSERT
-AFTER INSERT
-BEFORE UPDATE
-AFTER UPDATE
-BEFORE DELETE and
-AFTER DELETE

2. Differentiate between FLOAT and DOUBLE.

Ans. FLOAT stores floating point numbers with accuracy up to eight places and has four bytes while DOUBLE stores floating point numbers with accuracy upto 18 places and has eight bytes.

3. Explain MySQL architecture.

Ans. The MySQL pluggable storage engine architecture enables a database professional to select a specialized storage engine for a particular application need while being completely shielded from the need to manage any specific application coding requirements. The pluggable storage engine architecture provides a standard set of management and support services that are common among all underlying storage engines. The storage engines themselves are the components of the database server that actually perform actions on the underlying data that is maintained at the physical server level. 

The MySQL server architecture isolates the application programmer and DBA from all of the low-level implementation details at the storage level, providing a consistent and easy application model and API. Thus, although there are different capabilities across different storage engines, the application is shielded from these differences.

The front layer takes care of network connections and security authentications, the middle layer does the SQL query parsing, and then the query is handled off to the storage engine. A storage engine could be either a default one supplied with MySQL (MyISAM) or a commercial one supplied by a third-party vendor (ScaleDB, InnoDB, etc.).

4. Explain MySQL locks.

MySQL enables client sessions to acquire table locks explicitly for the purpose of cooperating with other sessions for access to tables, or to prevent other sessions from modifying tables during periods when a session requires exclusive access to them. Locks may be used to emulate transactions or to get more speed when updating tables. This is explained in more detail later in this section.

Table-level locks allow the user to lock the entire table, page-level locks allow locking of certain portions of the tables (those portions are referred to as tables), row-level locks are the most granular and allow locking of specific rows.

5. What is Heap Table.

Ans.  HEAP tables are found in memory.

  • – They are used for high speed storage on temporary basis.

Some of their characteristics are:

  • – They do not allow BLOB or TEXT fields.

– Only comparison operators like =, <,>, = >,=< , can be used with them.

  • – AUTO_INCREMENT is not supported by HEAP tables.

– Indexes should be NOT NULL.

6. Explain multi-version concurrency control in MySQL.

Ans. Multiversion concurrency control(MCCor MVCC), is a concurrency control method commonly used by database management systemsto provide concurrent access to the database and in programming languages to implement transactional memory.

If someone is reading from a database at the same time as someone else is writing to it, it is possible that the reader will see a half-written or inconsistent piece of data. There are several ways of solving this problem, known as concurrency controlmethods

Each row has two additional columns associated with it – creation time and deletion time, but instead of storing timestamps, MySQL stores version numbers.

7. What’s ACID?

Ans. 

Automicity – transactions are atomic and should be treated as one in case of rollback.

Consistency – the database should be in consistent state between multiple states in transaction.

Isolation – no other queries can access the data modified by a running transaction.

Durability – system crashes should not lose the data.

8. Which storage engines support transactions in MySQL?

Ans. Berkeley DB and InnoDB.

9. What are the advantages of MySQL in comparison to Oracle?

Ans.

  • – MySQL is open source software available at zero cost.

– It is portable.

  • – GUI with command prompt.

– Administration is supported by MySQL Query Browser.

10. What does myisamchk do?

Ans. It compresses the MyISAM tables, which reduces their disk or memory usage.

11. How can we convert between Unix & MySQL timestamps?

Ans. MySQL timestamp can be converted into Unix timestamp using the command UNIX_TIMESTAMP. Unix timestamp can be converted into MySQL timestamp using the command FROM_UNIXTIME.

12. What’s the difference between CHAR_LENGTH and LENGTH?

Ans. The first is, naturally, the character count. The second is byte count. For the Latin characters the numbers are the same, but they’re not the same for Unicode and other encodings.

13. MySQL is a relational database management system.

Ans. A relational database stores data in separate tables rather than putting all the data in one big storeroom. This adds speed and flexibility. The tables are linked by defined relations making it possible to combine data from several tables on request. The SQL part of MySQL stands for “Structured Query Language” – the most common standardized language used to access databases.

14. Why use MySQL?

Ans. 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.

15. What’s the difference between PRIMARY KEY and UNIQUE in MyISAM?

Ans.  PRIMARY KEY cannot be null, so essentially PRIMARY KEY is equivalent to UNIQUE NOT NULL

A table can have at most one PRIMARY KEYconstraint but it can have as many as you want UNIQUE KEYconstraints.

Another minor difference is that you can choose the name you want for a UNIQUE KEY constraint (and index). On the other hand, the PRIMARY KEY has the default name: PRIMARY.

Primary key disallows nullable columns

Unique key allows nullable columns

16. What’s the difference between query_cache_type 1 and 2?

Ans. The second one is on-demand and can be retrieved via SELECT SQL_CACHE. If you’re worried about the SQL portability to other servers, you can use SELECT /* SQL_CACHE */ id FROM ? – MySQL will interpret the code inside comments, while other servers will ignore it.

17. What are CSV tables?

Ans. CSV stands for Comma-Separated Values. CSV table stores data in plain text and tabular format. It typically contains one record per line.

Each record is separated by specific delimiters (Comma, Semi-colon, …) where each record has same sequence of field. CSV tables are most widely used to store phone contacts to Import and Export and can be used to store any sort of plain text data.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>