Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ MySQL optimization resulting order by using filesort     - Linux Getting Started tutorial: hard disk partition and to deal with traps (Linux)

- Use UDEV SCSI Rules configured ASM on Oracle Linux 5 and 6 (Database)

- Django url () function Detailed (Programming)

- CentOS7 installation hardware monitoring for Zabbix enterprise applications (Server)

- GitLab installation configuration notes (Linux)

- MySQL remote connection settings (Database)

- C language preprocessor command (Programming)

- C ++ Const breaking rules (Programming)

- Install MATE desktop environment adjustment tools Mate Tweak 3.3.6 (Linux)

- CentOS7 installed VMware 10 (Linux)

- Web cache basics: terminology, HTTP headers, and caching policies (Server)

- Python Multithreaded Programming (Programming)

- Ubuntu install Geary (Linux)

- Memcache explain in detail (Server)

- Five useful commands to manage file types and system time in linux (Linux)

- Use PuTTY to access the virtual machine Linux under Windows xp (Programming)

- Ubuntu cut screen method (Linux)

- Windows 8.1 hard drive to install Ubuntu 14.04 dual system reference tutorials and multi-drive Precautions (Linux)

- Windows7 system using Vagrant to build Linux virtualized development environment (Linux)

- Install Ubuntu Software Center App Grid (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:      
 
- 7 JavaScript interview questions (Programming)
- How to find an IP address through the command line (Linux)
- Java synchronization mechanism: synchronized, wait, notify (Programming)
- Java data structures - order linear form of table ArrayList (Programming)
- Java Learning: elegant string (Programming)
- CentOS install Memcached (Server)
- Android Service service applications and the phone SMS Listener Listener (Programming)
- How to install the latest version of the Eclipse in Ubuntu 14.04 (Linux)
- Use Visual Studio to compile and use WinGDB remote debugging embedded Linux programs (Programming)
- Linux common network tools: Scan routing of mtr (Linux)
- How to enable curl command HTTP2 support (Linux)
- Experts teach you safety precautions Linux Sniffer (Linux)
- Manually generate AWR reports (Database)
- Timing Nginx logs cut and remove the log records of the specified number of days before (Server)
- Ubuntu 12.04 64bit Install Source Insight 3.5 and create desktop icons (Linux)
- Java generate two-dimensional code by Zxing (Programming)
- How to Install Suricata IDS on a Linux system (Server)
- Close common port to protect server security (Linux)
- Physical backup and recovery SYSTEM table space (Database)
- Linux system security configuration (Linux)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.