Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ MySQL optimization resulting order by using filesort     - Pydev installed and configured on the Eclipse (Linux)

- Why is better than Git SVN (Linux)

- MySQL flip-flop (Database)

- Under CentOS yum install Nginx smooth switch mounted to Tengine (Server)

- Configuring Proxy on a Unix terminal, accelerate Android Studio Construction (Linux)

- Ten SCP transfer command example (Linux)

- Linux basic introductory tutorial ---- simple text processing (Linux)

- Manually compile Hadoop 2.6.0 under Ubuntu 14.04 (Server)

- sed and awk in shell usage and some examples (Linux)

- Lua and C ++ (Programming)

- Use Oracle 11g show spparameter command (Database)

- Ubuntu in Vim editor display processing method Chinese garbled (Linux)

- Git commands (Linux)

- Linux system on how to use rsync to synchronize data (Server)

- MySQL High Availability plan several options (Database)

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

- Ubuntu disable graphics card (Linux)

- Oracle 11g maintenance partitions (Seven) - Modifying Real Attributes of Partitions (Database)

- Oracle Automatic Diagnostic Repository (Automatic Diagnostic Repository, ADR) (Database)

- To obtain installation package (RPM) under RHEL6 (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:      
 
- To_teach you three strategies to prevent the LAN IP address theft (Linux)
- Android Studio Getting Started Hello World (Programming)
- Linux non-graphical interface to install Oracle Database (Database)
- Django url () function Detailed (Programming)
- MySQL and MariaDB new master from the cluster configuration GTID (Database)
- Difference LVS three scheduling modes (Server)
- Java, hashcode, equals and == (Programming)
- Reason C ++ program running under Linux a segmentation fault core dumped in (Programming)
- Repair after installing Ubuntu no boot device error (Linux)
- Postmodern systems programming language (Programming)
- Installation Elementary OS Freya to do some settings (Linux)
- Basic Operation Linux files and directories (Linux)
- ls command: 15 Level Linux interview question (Linux)
- How to set the default Fedora from the command line (Linux)
- rsync + inotify to achieve real-time synchronization (Server)
- Linux SSH login without a password (Linux)
- Let you Fun Ubuntu desktop eleven weapon (Linux)
- ASM learning overview (Database)
- Android Send HTTP POST requests (Programming)
- To use Java arrays implement the order form (Programming)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.