Priorities Within the WHERE Statement of MySQL

Prioritization within the WHERE clause of MySQL has a profound effect on query performance, especially in the case of the database is large or complex queries, in which MySQL processes the SQL statements from left to right.

The important principle of the priority of queries in this section is to specify the results that are ordered in ascending order from left query to right query.

I give an example of the member database.

Suppose there are 100 records in total. 50 men, 50 women and have a unique name.

I want information that is male.

SELECT * FROM user WHERE sex='m'

I will get 50 records. If I need information called test.

SELECT * FROM user WHERE username='test'

I will get only one record (if any).

The above command will execute a loop from the first record one by one and compare the data within the WHERE statement. Of course, it will need to process 100 cycles according to the amount of data.

Suppose, if I want the data named test male, I can write two types of queries.

SELECT * FROM user WHERE sex='m' AND username='test'
and
SELECT * FROM user WHERE username='test' AND sex='m'

If looking at the results of both commands, the result would be the same: 1 record in the case of a male test and 0 record in the case of a female test or no user name test.

Let's examine the working order.

In the case of 1, sex = 'm' AND username = 'test', MySQL will check if sex is equal to m or not. If it is found as m, then it will check if username is a test. In this case, it means that 100 times of checking for sex. Result: 50 times the name check is equal to 100 + 50 = 150 times (this value is an estimate for understanding)

In the case of 2 username = 'test' AND sex = 'm' in order of verification Will loop the first instruction for 100 times as before But will get the true result (username = 'test') to check only one sex record is equal to only 100 + 1 = 101 processing

In order to process the commands on a single table, you may not see the images much. But if cross-table checking is done, the number of commands to do will multiply because MySQL will loop through the commands one by one until the entire database is complete. Which will greatly increase the time consuming

Hopefully, next, consider the importance of queries in order to prevent MySQL from crashing.