MySQL index usage example analysis_MySQL

This article analyzes the usage of MySQL index with examples. Share it with everyone for your reference, the details are as follows:

MYSQL description:

An article library with two tables: category and article. There are 10 pieces of classified data in category. There are 200,000 items in article. There is an "article_category" field in article that corresponds to the "category_id" field in category. The word article_category has been defined as an index in the article table. The database size is 1.3G.

Problem description:

Execute a very common query:

The code is as follows:

SELECT * FROM `article` WHERE article_category=11 ORDER BY article_id DESC LIMIT 5

The execution time is about 5 seconds

Solution:

Create an index:

The code is as follows:

create index idx_u on article (article_category,article_id);

The code is as follows:

SELECT * FROM `article` WHERE article_category=11 ORDER BY article_id DESC LIMIT 5

reduced to 0.0027 seconds

Continue the question:

The code is as follows:

SELECT * FROM `article` WHERE article_category IN (2,3) ORDER BY article_id DESC LIMIT 5

The execution time is 11.2850 seconds.

Use OR:

select * from article
where article_category=2
or article_category=3
order by article_id desc
limit 5

Execution time: 11.0777

Solution: Avoid using in or or (or will cause table scanning), use union all

Use UNION ALL:

(select * from article where article_category=2 order by article_id desc limit 5)
UNION ALL (select * from article where article_category=3 order by article_id desc limit 5)
ORDER BY article_id desc
limit 5

Execution time: 0.0261

Note: The difference between UNION and UNION ALL

In the database, the UNION and UNION ALL keywords both merge two result sets into one, but they are different in terms of usage and efficiency.

UNION will filter out duplicate records after table linking, so after table linking, it will sort the result set generated, delete duplicate records and return the results.

In most actual applications, duplicate records will not be generated. The most common ones are process table and history table UNION. Such as:

select * from gc_dfys union select * from ls_jg_dfys

This SQL first retrieves the results of the two tables when running, then uses the sort space to sort and delete duplicate records, and finally returns the result set. If the table data is large, the disk may be used for sorting.

UNION ALL simply combines the two results and returns them. In this way, if there is duplicate data in the two result sets returned, the returned result set will contain duplicate data.

In terms of efficiency, UNION ALL is much faster than UNION, so if you can confirm that the two merged result sets do not contain duplicate data, then use UNION ALL, as follows:

select * from gc_dfys union all select * from ls_jg_dfys

Note: order by problem of union all in mysql

When I was writing mysql database code today, I found that the result of union was not expected

$stime = date("H:i:s");
$sql1 = "select * from T where '$stime'>stime order by stime desc";
$sql2 = "select * from T where stime>'$stime' order by stime asc";
$sql = "($sql) union all ($sql2)";

The result is correct when $sql1 and $sql2 are executed respectively

But when executing $sql, I found that the result was reversed, the part of $sql1 changed to ascending order, and the part of $sql2 changed to descending order

The search did not yield a satisfactory answer. It seems that some databases still do not support the word order by

I accidentally discovered that this works:

The code is as follows:

$sql = "select * from ($sql1) as temp1 union all select * from ($sql2) as temp2";

This is because your usage of union is incorrect. In a union operation, the order by statement cannot appear in two select statements combined by a union operation. Sorting can be done by specifying an order by clause after the second select statement.

Readers who are interested in more MySQL-related content can check out the special topics on this site: "Summary of MySQL Index Operation Skills", "Comprehensive Collection of MySQL Log Operation Skills", "Summary of MySQL Transaction Operation Skills", "Comprehensive Collection of MySQL Stored Procedure Skills", " Summary of MySQL database lock related skills" and "Summary of commonly used MySQL functions"

I hope this article will be helpful to everyone’s MySQL database planning.