Shulou(Shulou.com)06/02 Report--

This article mainly explains "the usage of the function of MYSQL query condition". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn the usage of the function of MYSQL query condition.

Accidentally think of a thing, the specific person and scene will not mention, the thing is a sentence, obviously is a very simple sentence, there is an index, verification is also very fast, but as long as in the program is slow to death. It was later found that a function was used after the statement, causing the problem. OK let's do a test to restore the scenario a little bit.

We create a table.

CREATE TABLE `rand_ table` (

`id`int (11) NOT NULL AUTO_INCREMENT

`msg_ code` varchar (20) DEFAULT NULL

`insert_ date` datetime DEFAULT NULL

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Create a stored procedure to populate the data

Create PROCEDURE insert_data ()

Begin

Declare i int

Declare msg int

Declare msg_ch varchar (20)

Set I = 1

Set msg = 1

While i

< 10000000 do set msg= floor(rand()*10000); set msg_ch=convert(msg,CHAR); insert into rand_table (msg_code,insert_date) values (msg_ch,now()); set i=i+1; end while; end 然后我们给这个"白开水的表"创建索引，并且查询，OK 一定是走索引的。

The purpose of the following statement is to randomly select a number to match a field in rand_table

Select * from rand_table where msg_code = floor (rand () * 1000)

The result can come out, no problem, but on the other hand, if you look at the implementation plan, more than 800,000 data should be scanned all over the table, which does not look good.

The person who wrote this statement suspected that there was a problem with the distribution of the data, but it was verified that it was not, and the filtered behind it could not be the problem. As you can see in the following figure, the missing is not caused by the distribution of the data.

Then exactly how this problem arises, MYSQL's in the query, because the latter function rand () is a random function, his feedback is also a random value, the relevant comparison is not a query after obtaining the value, but each row needs to be compared with the random value, although the random value should be consistent when comparing.

I think when I say here, some people have already held a skeptical mentality, wondering if this person is in the mood of nonsense to read this passage. OK, let's verify it. Here are two self-built functions to prove that what I said above is not nonsense. Note that there is no big difference between the two functions, just in

There is a difference on DETERMINISTIC, the first one below

DELIMITER $$

Create function pick_up_rand () returns int

DETERMINISTIC

BEGIN

RETURN floor (rand () * 1000)

End

$

DELIMITER $$

Create function pick_up_rand_n () returns int

NOT DETERMINISTIC

BEGIN

RETURN floor (rand () * 1000)

End

$

From the following figure, 1 proves that my point of view is correct, and it is true that uncertain values need to be scanned in the full table in MYSQL. 2 problems like this can be used to write a function and determine it to meet such requirements, as well as the possibility that the MYSQL query optimizer can choose an index.

At this point, I believe that we have a deeper understanding of the "MYSQL query condition function usage", might as well come to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.