Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ MySQL optimization resulting order by using filesort     - MongoDB3.0.x version of the user authorization profile (stand-alone environment) (Database)

- SVN hook code set to synchronize Web directory (Server)

- The correct way to open Xcode - Debugging (Programming)

- Safety testing Unix and Linux server entry succinctly (Linux)

- Thrift 0.9.3 compiler installation under Ubuntu (Linux)

- FileZilla 3.10.1.1 install on Ubuntu 14.10 (Linux)

- Bash How to read a file line by line (Programming)

- Teach you how to ensure password security under the Linux operating system (Linux)

- Linux Programming memory mapping (Programming)

- Java to create a table in the database SYBase (Database)

- Linux startup and logon security settings (Linux)

- C ++ stderr / stdout redirected to a file (Programming)

- Graphical development environment to build Android under Ubuntu 11.04 (Linux)

- Physical backup and recovery SYSTEM table space (Database)

- Use GLOBK batch command network adjustment (Linux)

- Linux system security configuration (Linux)

- Experience PHP 7.0 on CentOS 7.x / Fedora 21 (Server)

- Installation of Python2.7.10 under CentOS 6.4 (Linux)

- Plasma 5.4 How to install on Kubuntu 15.04 (Linux)

- Analyzing Linux server architecture is 32-bit / 64-bit (Server)

 
         
  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:      
 
- Java threads in the life cycle (Programming)
- How to use secure FTP file transfer (Server)
- To compile and install OpenCV-2.3.1 FFmpeg-2.1.2 under CentOS (Linux)
- MongoDB fragmentation (Cluster) (Database)
- Docker: Mirroring and container operations (Server)
- Slice MyCAT common fragmentation rules of enumeration (Database)
- Android Fragment really fully resolve (Programming)
- Compile and install Redis and register as a system service under RedHat5.8 environment (Database)
- Broadcom transplanted to OpenWrt summary (Programming)
- Linux kernel netpoll framework netconsole (Linux)
- Gnu Linux - Ubuntu System Clean-term consolidation (Linux)
- Hackers is how the invasion and control of Things devices? (Linux)
- Everyone should know something about TCP (Linux)
- Ftp user to create multiple virtual machines to support different access rights Examples (Server)
- STL spatial Configurator (Programming)
- Ubuntu 14.04 set auto sleep time (Linux)
- Kubernetes (k8s) environment to build combat (Server)
- Linux System Getting Started Tutorial: How do you know Shell which is currently being used (Linux)
- Spring AOP custom annotation way to achieve log management (Programming)
- Teach you how to synchronize Microsoft OneDrive in Linux (Linux)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.