How MySQL Works?

Introduction to MySQL

A database is a collection of data stored in an organized format. A relational database is a database that stores data and the relations between them in tabular form. RDBMS is a software used to manage and edit databases based on the relational model and MySQL is one of the very popular RDBMS software written in C and C++. It is a highly flexible, efficient, scalable, secure, and easy-to-use database management system. You can save and edit the data stored in tables, rows, and columns.

Let's take a look at some of the key features that make MySQL so popular:

1. Open Source and Free:
   MySQL is available as open source, so you can easily download, use, and modify it for your personal use. It is free to use, so you can download it for free from the MySQL official website.
2. Easy to use:
   It is an easy-to-understand software that you can learn and modify as needed.
3. High scalability:
   MySQL is designed to work with large databases, and can work with small datasets, that can be easily scaled to larger datasets as needed.
4. Secure:
   Software security speaks a lot for its popularity and MySQL provides a secure interface with the help of a flexible password system which is handled in an encrypted format while connecting to the server. It has a host-based guarantee that is first validated before accessing the database.
5. Highly flexible:
   MySQL is very flexible as it supports a large number of embedded applications.

History of MySQL

MySQL was founded by a Swedish company called MySQL AB by Michael Widenius. Its first version was released in 1995 and it went open source in the year 2000 along with the release of alpha-beta versions.

In 2001, MySQL quickly became popular, reaching 1 million active installations, and in 2002 it surpassed about 3 million active users and generated about $6.5 million in revenue. MySQL was also deployed at the US headquarters along with the Swedish headquarters. In 2003, MySQL recorded 30K downloads and $4 million installations daily which took its revenue to over $12 million. During this time, MySQL formed a partnership with SAP that lasted about three years, ending in 2007. During this time, various features in MySQL were produced along with SAP. MySQL is now owned and supported by Oracle after its acquisition in 2010.

Architecture of MySQL

MySQL follows a client-server architecture in which a server executes and provides services requested by clients over a network. This is a three-tier process where the client first makes a request, then the server processes the request, and finally, the response is generated and sent back to the client screen.

Both client and server applications interact directly by forming a communication channel using transport layer protocols. This process establishes a connection and allows entities to send and receive information. With MySQL, the client can make requests through a graphical user interface or retrieve the data using SQL queries, and the server can give the desired output for the request.

Digging deeper, there are three tiers, client-side, server-side, and database storage-side, where data is stored and retrieved by the server as needed. Let's understand the use of each layer in detail:

1. Client side:
   The client side of the architecture is the end user who uses a web browser to enter the desired URL. Here, the user uses a graphical user interface (GUI) screen using web-based or desktop-based clients like phpMyAdmin or Workbench, or by using a command line terminal to submit various MySQL commands and produce the output from the server side.

   An error message is generated if the command entered is invalid and is displayed on the client's screen. For each valid command, the valid output is generated and displayed on the screen. A user sends a request to the server, which, after reviewing and accepting the request, establishes a communication channel for further requests.

2. Server side:
   The server side of the MySQL architecture is the actual requests handler that comprises the logic of the database system. It is also known as the brain of MySQL architecture as it also takes care of optimizing queries to ensure that they execute quickly and efficiently. It uses various techniques to optimize query execution, such as index selection, query caching, and join optimization. It receives every request sent by the client and also returns the result after processing the request.

   The server is also responsible for storing the data on disk securely and durably so that it is efficient and easily manageable.

3. Database storage side:
   A MySQL database contains a storage module that is used to store data in the form of tables in the database system. The storage side makes it easy for the server side to retrieve the data when needed.

How MySQL Processes Queries?

MySQL uses Structured Query Language (SQL) as its default query language to communicate with databases and retrieve results. When you write a query in SQL, MySQL processes this query and returns the results in the form of a table with rows and columns.

If you want to see exactly how MySQL works and processes your query, you can add the EXPLAIN keyword in front of your SQL query to see the detailed steps MySQL takes to get the answer for that query. The EXPLAIN command works with SELECT, INSERT, REPLACE, UPDATE, and DELETE statements in SQL queries to provide information about the execution steps. To see how MySQL works, let's first create a simple query in MySQL with the following three tables:

The student_details table holds the information for the student's roll number, first name, and last name.

The marks table contains the information for the student's roll number, subject name, and the marks obtained.

The address_details table stores the information for the student's name and the city.

You can use the below query to see how MySQL works and processes the results, using the above three tables:

The given SQL query is trying to display the first name of the student and their roll number from the student_details and marks tables, where the first_name is 'Vishnu' and the city in the address_details table is 'Bisalpur'. This is achieved through the use of SQL JOINs, which combine related data from multiple tables based on a common field, in this case, the roll_number column from the student_details and marks tables.

Now, when we add the EXPLAIN keyword before a SELECT statement, it provides information on how MySQL executes the query. It displays details about the execution plan of the query, such as the order in which tables are accessed, the indexes used, and the number of rows examined. It can help identify potential performance issues or optimization opportunities in the query. Let's see the results by adding the EXPLAIN keyword in our query:

The output of the above query would be:

Here, the id field indicates the position of the SELECT within the full query and the table field indicates the name of the table being queried. The select_type field indicates the type of query, for example, a simple query (not having subqueries), a subquery, an outer query, a subquery with or without an outer query, a UNION, etc. The type field indicates how the join will be performed ranging from a complex join covering all records or a join fetching the single matching record only. The possible_keys field specifies the indexes that MySQL can use to speed up the searches to bring optimization and the key field specifies the key to be used, with the length of the key shown in the key_len field.

The Rows field specifies the number of rows MySQL must examine in the corresponding table to successfully execute the query. To get the total number of rows in the table, MySQL has to scan and process the full query.

The filtered column in the EXPLAIN output indicates the percentage of rows that are expected to be examined by the MySQL optimizer while executing the query and the Extra field contains extra details about how MySQL processes the query. For example, if it is using WHERE clauses, using indexes, or by using temporary tables, and so on.

By looking at the output of the EXPLAIN command, you can better understand the functionality and see if there is a scope for improvement in query execution to reduce the scan calls. You can reduce the number of scans by introducing an index on a table by updating the table's possible_keys from null to the primary key of that table. After adding the relevant columns as primary keys, the number of rows to scan can be reduced to 1 per row.

Let's take a look at some of the limitations of the EXPLAIN keyword in MySQL:

1. No information about how triggers and stored functions affect the requests is provided by the EXPLAIN keyword.
2. It does not tell about the optimizations that MySQL performs while executing the query.
3. The EXPLAIN keyword does not work with stored procedures and produces highly imprecise estimate statistics.
4. It doesn't provide all the information about the query's execution plan.

See Also

1. MySQL installation
2. MySQL features
3. MySQL versions