Skip to main content

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.
Labels:

Comments

Post a Comment

Popular posts from this blog

Changing AppServ Path Directory

Usually when we install AppServ on our machine Important instructions for installation are Use the given value in the program as the best, which will allow us to The directory that stores files on our device is "C:\AppServ\www" which, if we want to change this file store to another place Let us do as follows Go to Start Menu -> Programs -> AppServ -> Apache Configure Server -> Edit the Apache httpd.conf Configuration File. Clicking will open the file httpd.conf and edit it with NotePad. Let us use Replace to search and replace. "C:/AppServ/www" with the new directory name that we want. For example, "D:/www", every character is assigned to the new directory "D:\www" After that, save the file to the same name and then restart the Apache is complete. Now that we have a new directory that holds our files as "D:\www" as needed, let us put the index.php file into this directory. And then test by typing http://l...
Post a Comment
Read more

Create Multiple Domains on Localhost

Usually, when we install Appserver, we will get the domain name is  http://localhost  If we want to change to a different name, such as  http://project  Or when wanting to have multiple domains to use with multiple projects, what to do? For example,  http://project1  Keep the file in the project1 directory and  http://project2  Keep files in project2. Managing or testing is probably a lot easier. Especially if there are many projects and the work will be a lot easier The basic principle is similar to making a subdomain on localhost : 1. Open the file  C:\windows\system32\drivers\etc\hosts  with a general text editor and add the desired domain, such as 127.0.0.1 project1 127.0.0.1 project2 And save Can be added according to the number of projects desired And the desired name (Including being able to create subdomain too) 2. Open Appserver's httpd.conf file. Don't know where it is. You can look at the Appserv...
Post a Comment
Read more