MySQL basic paging query method and its Optimization _ MySQL

Today, an oracle database is generated to mySQL. because the code is primitive and the database is accessed by JDBC, the paging query of the database cannot be found at once. Sorrow! First, let's take a look at the previously queried code: publicPageModelfindUserLis. Today, an oracle database is generated to mySQL, because the code is primitive and the database is accessed by JDBC, the paging query of the database cannot be found at once. Sorrow!

First look at the previously queried code:

public PageModel
 
   findUserList(int pageNo,int pageSize) {StringBuffer sbSql=new StringBuffer();sbSql.append("Select user_id,user_name,password,contact_tel,email,create_date ").append("From").append("(").append("Select rownum rn,user_id,user_name,password,contact_tel,email,create_date ").append("From").append("(").append("Select user_id,user_name,password,contact_tel,email,create_date from t_user where user_id <> 'root' ").append(" order by user_id").append(")where rownum <=?").append(") where rn>?");Connection conn=null;PreparedStatement pstmt=null;ResultSet rs=null;PageModel
  
    pageModel=null;try{conn=DbUtil.getConnnection();pstmt=conn.prepareStatement(sbSql.toString());pstmt.setInt(1, pageNo*pageSize);pstmt.setInt(2, (pageNo-1)*pageSize);rs=pstmt.executeQuery();List
   
     userList=new ArrayList
    
     ();while(rs.next()){User user=new User();user.setUserId(rs.getString("user_id"));user.setUserName(rs.getString("user_name"));user.setPassword(rs.getString("password"));user.setContactTel(rs.getString("contact_tel"));user.setEmail(rs.getString("email"));user.setCreateDate(rs.getTimestamp("create_date"));userList.add(user);}pageModel=new PageModel
     
      ();pageModel.setList(userList);pageModel.setTotalRecords(getTotalRecords(conn));pageModel.setPageNo(pageNo);pageModel.setPageSize(pageSize);}catch(SQLException e){DbUtil.close(rs);DbUtil.close(pstmt);DbUtil.close(conn);}return pageModel;}
     
    
   
  
 

Basically, it is similar to the rownum method of the previous SQL server database. But mysql won't work like this. use limit for paging.

First, let's look at my table structure:

PS: I added an index on user_id.

Then, use the unoptimized limit for query:

        #create INDEX rowindex on t_user(user_id)SELECT * from t_user ORDER BY USER_ID DESC limit 0,2

Then we optimize the query:

1. use the subquery method to optimize the query

SELECT * FROMt_userWHEREUSER_ID <= (SELECTUSER_IDFROMt_userORDER BYUSER_ID DESCLIMIT ($ page-1) * $ pagesize. ", 1), 1) ORDER BYUSER_ID DESCLIMIT $ pagesize for example: SELECT * FROMt_userWHEREUSER_ID <= (SELECTUSER_IDFROMt_userORDER BYUSER_ID DESCLIMIT 3) ORDER BYUSER_ID DESCLIMIT 3

II. Optimization using join

SELECT * FROMt_user AS u1JOIN (SELECTuser_idFROMt_userORDER BYUSER_ID DESCLIMIT ($ page-1) * $ pagesize. ", 1), 1) AS u2 example: SELECT * FROMt_user AS u1JOIN (SELECTuser_idFROMt_userORDER BYUSER_ID DESCLIMIT 0, 1) AS u2

3. optimize the query of the total number of returned data entries

Usually in the code, if I want to paging, I need to return a result set containing the total number of data items so that I can display the total number of data pages on the page based on the current pageSize.

For the query of the total number of data items, we usually use count (*) or count (0). However, in mysql, a built-in function is provided, to optimize this query:

SELECT SQL _CALC_FOUND_ROWS * from t_user where USER_ID <'root' limit 1; SELECT FOUND_ROWS (); # The Second returned result set is the number of returned results without limit