MySQL database limit and join optimization scheme

The following articles mainly describe the actual limit and join optimization solutions in the MySQL database. We all know that the actual application proportion still accounts for the majority. If you use this technology, if you are curious, the following articles will unveil its mysteries.

MySQL limit will be used for paging in PHP. Most of them are familiar with "select * from title where uid = ** order by id desc limit m, n, not all of them can tell what is wrong, but it should be done with a large amount of data, for example, millions of users like "select * from title where uid = 177 order by id desc limit 1234567,20" will find that the SQL Execution time has obviously become very long. Why?

Starting with the MySQL database's limit principle, MySQL first scans (m + n) records using limit m and n rows starting from m rows. for example, the preceding example is to scan 1234587 pieces of data first. Can the SQL statement be faster? This requires us to minimize the m value as much as possible, even without m directly limit n such as SQL.

Let's look at an example:

 
 

  
  
mysql> select id,substr(mobile from 1 for 7),time,cpid,linkid from cp_mo100227 where cpid=769 limit 888888,10;  
  
  
+———-+—————————–+———————+——+———————-+  
  
  
| id | substr(mobile from 1 for 7) | time | cpid | linkid |  
  
  
+———-+—————————–+———————+——+———————-+  
  
  
| 11535090 | 1353554 | 2010-02-24 21:07:48 | 769 | 21064905903309587933 |  
  
  
| 11535091 | 1353750 | 2010-02-24 21:07:48 | 769 | 21064912943389480033 |  
  
  
| 11535093 | 1353394 | 2010-02-24 21:07:48 | 769 | 21064912945389480075 |  
  
  
| 11535098 | 1343073 | 2010-02-24 21:07:50 | 769 | 21064905865309587977 |  
  
  
| 11535100 | 1369270 | 2010-02-24 21:07:51 | 769 | 21064926770369210194 |  
  
  
| 11535103 | 1355683 | 2010-02-24 21:07:51 | 769 | 21064912944389480113 |  
  
  
| 11535104 | 1368959 | 2010-02-24 21:07:51 | 769 | 21064902508384448468 |  
  
  
| 11535105 | 1365243 | 2010-02-24 21:07:51 | 769 | 21064905907309403124 |  
  
  
| 11535106 | 1362145 | 2010-02-24 21:07:52 | 769 | 21065002511384448497 |  
  
  
| 11535107 | 1369228 | 2010-02-24 21:07:52 | 769 | 21064902514384448437 |  
  
  
+———-+—————————–+———————+——+———————-+  
  
  
10 rows in set (3.84 sec)  
  
  
 
  
  
mysql> select id,substr(mobile from 1 for 7),time,cpid,linkid from cp_mo100227 where cpid=769 and id>=11535090 limit 10;  
  
  
+———-+—————————–+———————+——+———————-+  
  
  
| id | substr(mobile from 1 for 7) | time | cpid | linkid |  
  
  
+———-+—————————–+———————+——+———————-+  
  
  
| 11535090 | 1353554 | 2010-02-24 21:07:48 | 769 | 21064905903309587933 |  
  
  
| 11535091 | 1353750 | 2010-02-24 21:07:48 | 769 | 21064912943389480033 |  
  
  
| 11535093 | 1353394 | 2010-02-24 21:07:48 | 769 | 21064912945389480075 |  
  
  
| 11535098 | 1343073 | 2010-02-24 21:07:50 | 769 | 21064905865309587977 |  
  
  
| 11535100 | 1369270 | 2010-02-24 21:07:51 | 769 | 21064926770369210194 |  
  
  
| 11535103 | 1355683 | 2010-02-24 21:07:51 | 769 | 21064912944389480113 |  
  
  
| 11535104 | 1368959 | 2010-02-24 21:07:51 | 769 | 21064902508384448468 |  
  
  
| 11535105 | 1365243 | 2010-02-24 21:07:51 | 769 | 21064905907309403124 |  
  
  
| 11535106 | 1362145 | 2010-02-24 21:07:52 | 769 | 21065002511384448497 |  
  
  
| 11535107 | 1369228 | 2010-02-24 21:07:52 | 769 | 21064902514384448437 |  
  
  
+———-+—————————–+———————+——+———————-+  
  
  
10 rows in set (0.00 sec)  
  
  
 
  
  
mysql> select id,substr(mobile from 1 for 7),time,cpid,linkid from cp_mo100227 where cpid=769 and time>=’2010-02-24 21:07:48′ limit 10;  
  
  
+———-+—————————–+———————+——+———————-+  
  
  
| id | substr(mobile from 1 for 7) | time | cpid | linkid |  
  
  
+———-+—————————–+———————+——+———————-+  
  
  
| 11535090 | 1353554 | 2010-02-24 21:07:48 | 769 | 21064905903309587933 |  
  
  
| 11535091 | 1353750 | 2010-02-24 21:07:48 | 769 | 21064912943389480033 |  
  
  
| 11535093 | 1353394 | 2010-02-24 21:07:48 | 769 | 21064912945389480075 |  
  
  
| 11535098 | 1343073 | 2010-02-24 21:07:50 | 769 | 21064905865309587977 |  
  
  
| 11535100 | 1369270 | 2010-02-24 21:07:51 | 769 | 21064926770369210194 |  
  
  
| 11535103 | 1355683 | 2010-02-24 21:07:51 | 769 | 21064912944389480113 |  
  
  
| 11535104 | 1368959 | 2010-02-24 21:07:51 | 769 | 21064902508384448468 |  
  
  
| 11535105 | 1365243 | 2010-02-24 21:07:51 | 769 | 21064905907309403124 |  
  
  
| 11535106 | 1362145 | 2010-02-24 21:07:52 | 769 | 21065002511384448497 |  
  
  
| 11535107 | 1369228 | 2010-02-24 21:07:52 | 769 | 21064902514384448437 |  
  
  
+———-+—————————–+———————+——+———————-+  
  
  
10 rows in set (0.01 sec) 
 
 

In this example, the data table id is the primary key and the time is also indexed. The total data in the table is about 769 rows, and of the data with the cpid is about rows. the id and time here may be discontinuous. therefore, you cannot directly obtain the id> m.

So you can display "1, 2, 3, 4, 5, last page" or "homepage, <100,101,102,103> last page", which can greatly reduce the m value!

The join statement in MySQL is, by the way, usually a little bit of attention is to use small tables to drive large tables, the principle of MySQL join is to execute a loop. For example, left join is to cyclically drive the table on the right of the data on the left. For example, m records may be matched on the left, there are n records on the right, which is m cycles. n rows of data are scanned each time, and the total number of rows of data scanned is m * n rows. the size of the returned result set on the left determines the number of loops. Therefore, it is not necessarily correct to use a small table to drive a large table.

The result set of a small table may be larger than the result set of a large table. Therefore, when writing a join operation, try to estimate the possible result set of the two tables as much as possible, and use a small result set to drive the large result set. it is worth noting that when left/right join is used, the conditions of the slave table should be written after on, and the master table should be written after where. otherwise, the MySQL database will be used as a normal join Table query!