Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ MySQL optimization resulting order by using filesort     - Linux commands to access the cheat sheet (Linux)

- Puppet 3.x installed on Debian 7 (Server)

- On the PC goes heavy security watch your startup items (Linux)

- Java proxy mode (Programming)

- Java concurrent programming combat (using synchronized synchronization method) (Programming)

- Grading defense against Linux server attacks (Linux)

- Explore Android SQLite3 multithreading (Programming)

- Ubuntu 14.04 to install Blender 2.71 (Linux)

- Configuration OpenOCD + FT2232 under Ubuntu (Linux)

- Installation under Linux Mint system guidelines for Gtk (Linux)

- Boot-Repair Tool - repair of frequent start-up problems (Linux)

- SSL VPN SSL VPN access to security websites patron (Linux)

- CentOS7 installation performance monitoring system (Server)

- Use Ansible installation NGINX and NGINX Plus (Server)

- Linux system security check method (Linux)

- Debian 8 (amd64) installation deployment Memcached management tools MemAdmin (Server)

- How to use the Linux terminal Git commands (Linux)

- Oracle 10g, 11g database silent installation of small differences (Database)

- Installation of Gitlab under Ubuntu (Linux)

- Redmine Installation (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:      
 
- Using LLVM Clang and Blocks under Linux (Programming)
- Using IntelliJ IDEA Import Spark Spark latest source code and compile the source code (Linux)
- How to use the on-screen keyboard in Linux (Linux)
- Plasma 5.4 How to install on Kubuntu 15.04 (Linux)
- Radius server setup under CentOS (Server)
- STL in the list of erase () method (Programming)
- 10 useful Linux command Interview Questions and Answers (Linux)
- Ubuntu 14.04 installed Nvidia CUDA 7.5 and build Python Theano deep learning development environment (Linux)
- Postgres-X2 deployment steps (Database)
- Linux 64-bit porting (Programming)
- Learning the Linux powerful network management capabilities (Linux)
- MongoDB3.0.x version of the user authorization profile (stand-alone environment) (Database)
- Production environment Linux bonding method standby mode slave card switch (Server)
- ORA-01839 error caused by incorrect system date setting (Database)
- Hadoop2.6.3 build clusters and the development of MapReduce WIN7 by Eclipse on Linux demo (Server)
- GRUB2 boot Ubuntu Manual (Linux)
- PostgreSQL Stream Configuration (Database)
- Ubuntu security settings and tools (Linux)
- How to install Kernel 4.0.2 on CentOS 7 (Linux)
- CentOS6 installation configuration rsh (Server)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.