Essential MySQL FAQ: Core Concepts, Features, and Best Practices

1) What is MySQL?

MySQL is a multi‑threaded, multi‑user SQL database management system with over 11 million installations, making it the world’s second‑most popular open‑source database. The name comes from the founder Michael Widenius’s daughter, My, combined with SQL (Structured Query Language). MySQL is free for developers, while enterprises pay a license to Oracle.

Originally owned by MySQL AB, it was acquired by Sun Microsystems, which was later acquired by Oracle, the current owner.

MySQL is an Oracle‑supported relational database management system (RDBMS) that runs on many operating systems, most notably Windows, Linux, and UNIX. Although it can be used for various applications, it is primarily employed in web applications and online publishing, forming the database layer of the LAMP stack.

2) Which language is MySQL implemented in?

MySQL is mainly written in C and C++, while its SQL parser is generated by yacc.

3) What are MySQL’s technical features?

Flexible data structures

High performance

Ease of use and management

Flexible replication and high availability

Storage management and security

Drivers

Graphical tools

Enterprise‑level monitoring

Enterprise‑level security

JSON support

OLTP and transaction support

Geo‑spatial support

4) How does MySQL differ from SQL?

SQL is a standard query language used to interact with databases; MySQL is a concrete database system that implements SQL.

MySQL stores data and ensures its safety, often accessed via PHP scripts, whereas SQL itself is just a language.

SQL is a language; MySQL is software/application.

SQL creates database management systems; MySQL enables data processing, storage, deletion, and modification.

5) What is the difference between a database and a table?

A table is a way of organizing data within a database; a database is a collection of tables and the data they contain.

Tables group related rows and columns to form datasets that reside inside a database. The data stored in a table is part of the database, and vice‑versa.

A database is an organized collection of data and the means to access it, while a table is the structure that holds rows and columns of that data.

6) Why use MySQL server?

It is free for developers; enterprises pay only a modest license fee.

It is open source.

The community is large and responsive, providing quick help.

It has a long‑standing, stable release history with bugs regularly fixed.

It is fast, reliable, easy to use, and can be freely downloaded.

7) What storage engines does MySQL provide?

By default, MySQL 5.5 and later use InnoDB; earlier versions used MyISAM. Common engines include:

InnoDB

MyISAM

MEMORY

MERGE

8) What is the difference between CHAR and VARCHAR?

CHAR is fixed‑length; VARCHAR is variable‑length.

They differ in storage and retrieval behavior.

CHAR length is defined at table creation (1‑255 characters).

CHAR values are right‑padded with spaces; trailing spaces are removed on retrieval.

CHAR uses static memory allocation; VARCHAR uses dynamic allocation.

CHAR can be about 50 % faster than VARCHAR.

9) What is the difference between TRUNCATE and DELETE?

TRUNCATE is a DDL command; DELETE is DML.

TRUNCATE cannot use a WHERE clause; DELETE can.

TRUNCATE cannot be used on indexed views; DELETE can.

DELETE removes rows one by one; TRUNCATE removes all rows instantly and is more dangerous because it cannot be rolled back in some engines.

10) How many triggers are allowed in MySQL?

MySQL permits up to six triggers per table:

Before Insert

After Insert

Before Update

After Update

Before Delete

After Delete

11) What is a memory (HEAP) table?

A memory table resides in RAM and is created by specifying ENGINE=HEAP. It is used for fast temporary storage and does not support BLOB or TEXT columns.

12) What are BLOB and TEXT types?

BLOB (Binary Large Object) stores binary data. MySQL defines four BLOB sizes: TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB, differing by maximum length.

TEXT is a non‑binary string type with its own four sizes: TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT. TEXT values have a character set and are compared according to collation rules.

13) What is a trigger in MySQL?

A trigger is a set of statements that automatically executes in response to a specific event such as INSERT, UPDATE, or DELETE.

14) How do memory tables differ from temporary tables?

Memory tables exist in RAM, are temporary, cannot contain BLOB/TEXT, do not support AUTO_INCREMENT, and require non‑NULL indexes. They are shared among client sessions.

Temporary tables are also temporary but are stored on disk, can contain BLOB/TEXT, are scoped to a single session, and are dropped automatically when the session ends.

Main differences: memory tables are shared and act as a storage engine; temporary tables require the CREATE TEMPORARY privilege and are not shared.

15) What is the difference between FLOAT and DOUBLE?

FLOAT stores up to 8 digits of precision in 4 bytes; DOUBLE stores up to 18 digits of precision in 8 bytes.

16) What advantages does MySQL have over Oracle?

MySQL is free, fast, reliable, and open source; Oracle is expensive, though it offers a free edition.

MySQL can run on a laptop with less than 1 MB RAM, whereas Oracle 9i needs about 128 MB.

MySQL is well‑suited for web‑based databases; Oracle targets enterprise environments.

MySQL is highly portable.

17) What are MySQL’s disadvantages?

Performance may degrade with very large databases.

Versions prior to 5.0 lack COMMIT and stored procedures.

Transaction processing efficiency is lower.

Functionality depends heavily on plugins.

Development is not community‑driven.

18) CHAR vs VARCHAR differences (reiterated)

Storage and retrieval differ.

CHAR is fixed‑length, VARCHAR is variable‑length.

CHAR max length 255 characters; VARCHAR up to 4000 characters.

CHAR is about 50 % faster.

CHAR uses static memory allocation; VARCHAR uses dynamic allocation.

19) Difference between mysql_connect and mysql_pconnect

mysql_connect

Opens a new connection each time it is called.

Connection is opened and closed per request.

Each page load creates a new connection.

mysql_pconnect

The “p” stands for persistent; the connection remains open across requests.

Cannot be explicitly closed.

Useful for high‑traffic sites because it reduces connection overhead.

20) What does the “i_am_a_dummy” flag do?

If a WHERE clause is omitted, the “i_am_a_dummy” flag prevents MySQL from executing UPDATE or DELETE statements, protecting against accidental removal of an entire table.

21) How to get the current date in MySQL?

Use the function

SELECT CURRENT_DATE();

22) Security recommendations when using MySQL

Install antivirus software and configure the OS firewall.

Never run the MySQL server as the UNIX root user.

Change the root username and password, and disable remote root access.

23) How to change an existing user’s password with mysqladmin

mysqladmin -u root -p password "newpassword"

24) Difference between Unix timestamp and MySQL timestamp

Both are stored as 32‑bit integers, but a MySQL TIMESTAMP is displayed in a readable “YYYY‑MM‑DD HH:MM:SS” format.

25) How to find the N‑th highest salary in a MySQL table

Assuming a table named employee:

To retrieve the N‑th highest salary:

SELECT DISTINCT salary FROM employee ORDER BY salary DESC LIMIT n-1,1;

For the third highest salary:

SELECT DISTINCT salary FROM employee ORDER BY salary DESC LIMIT 2,1;