Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ MySQL optimization resulting order by using filesort     - Intel Graphics Installer 1.0.3 released, support for Ubuntu (Linux)

- PostgreSQL vacuum principle of a function and parameters (Database)

- Go Languages Reviews (Programming)

- PL / SQL data types (Database)

- Ubuntu 14.10 used ifconfig commands to manage your network configuration (Linux)

- The Java utility, JavaMail (Programming)

- TL-WR703N to install OpenWrt process notes (Linux)

- Implement firewall function on a closed Linux machine (Linux)

- How to Install Focuswriter 1.4.5 (Linux)

- Linux source code to install Apache and solutions to common errors (Server)

- Linux environment variable configuration and save places (Linux)

- Linux, modify / etc / profile file (Linux)

- C ++ Learning Notes: references (Programming)

- Linux environment variables inside (Linux)

- awk pattern matching (Programming)

- JBPM6 Installation and Configuration Tutorial (Linux)

- CentOS directory structure Explanation (Linux)

- Execute command sentence can result in equipment permanently bricked in Linux laptop (Linux)

- The YUM package management under Linux (Linux)

- Basic Tutorial: Linux novice should know 26 commands (Linux)

 
         
  MySQL optimization resulting order by using filesort
     
  Add Date : 2017-08-31      
         
         
         
  Using filesort generally appear in the order by use of statement.

mysql using filesort necessarily cause performance problems. But if so many queries, then every sort in mysql, or have an impact.

Here is a way to optimize the indexing fields in order by, for example, the statement:

SELECT * FROM yw_syjgb ORDER BY result_date desc LIMIT 0,1000;

 View the execution plan:

------------- + ---------- + ---- + ------ + ------------ + --- + ------ + ------ + --------- + --------- + ------------ ---- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
------------- + ---------- + ---- + ------ + ------------ + --- + ------ + ------ + --------- + --------- + ------------ ---- +
| 1 | SIMPLE | yw_syjgb | ALL | NULL | NULL | NULL | NULL | 1312418 | Using filesort |
------------- + ---------- + ---- + ------ + ------------ + --- + ------ + ------ + --------- + --------- + ------------ ---- +


We need to create indexes result_date:

At this view the execution plan:

+ ---- + ------- + ------------- + ---------- + ----------- ---- + ------------- + --------- + ------ + ------ + ------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------- + ------------- + ---------- + ----------- ---- + ------------- + --------- + ------ + ------ + ------- +
| 1 | SIMPLE | yw_syjgb | index | NULL | result_date | 6 | NULL | 1000 | NULL |
+ ---- + ------- + ------------- + ---------- + ----------- ---- + ------------- + --------- + ------ + ------ + ------- +

Using filesort can not see the implementation of the plan after using an index

Note: Because Using filesort is using an algorithm to sort in memory, MySQL for size sorting records is also done limitations: max_length_for_sort_data, the default is 1024
show variables like '% max_length_for_sort_data%';

+ --------- + ------- +
| Variable_name | Value |
+ --------- + ------- +
| Max_length_for_sort_data | 1024 |
+ --------- + ------- +
After I tested, if you sort the query data in the two defaults, then it will use Using filesort.

A summary, when the amount of data to sort the query in the range is the default value in the sort field indexed can enhance MySQL query speed.
     
         
         
         
  More:      
 
- CentOS x86 64bit upgrade to 2.7 Python2.6 (Linux)
- Safety testing Unix and Linux server entry succinctly (Linux)
- Debian installation (Linux)
- tespeed - test speed of Python tools (Linux)
- CentOS6 5 source compiler installation Hadoop2.5.1 (Server)
- Source code is installed MySQL 5.6.28 (Database)
- Linux compiler installation Redis (Database)
- OpenSSL for secure transmission and use of skills of files between Windows and Linux (Linux)
- Java rewrite equals method (Programming)
- Redis data types Introduction (Database)
- 20 Linux commands interview questions and answers (Linux)
- Installation Atom text editor on Mint Ubuntu / Linux (Linux)
- Oracle 11g on Linux system boot from the startup settings (Database)
- A list of the basics of Python, Ganso, Dictionary (Programming)
- JBPM6 Installation and Configuration Tutorial (Linux)
- How ONLYOFFICE collaborative editing document on Linux (Linux)
- Ubuntu 14.10 used ifconfig commands to manage your network configuration (Linux)
- Linux operating system security settings initial understanding (Linux)
- Docker deployment practices in Ubuntu (Server)
- Node.js simple interface server (Server)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.