Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ MySQL optimization resulting order by using filesort     - Orabbix binding Python send graphical reports (Linux)

- Zabbix configuration of SNMP (Server)

- Detailed software to run UnixBench (Linux)

- High-performance open-source http accelerator Varnish introduce (Server)

- Initialization and starting process of Linux (Linux)

- STL spatial Configurator (Programming)

- MySQL primary and secondary replicate data inconsistencies (Database)

- ARM constant expression (Programming)

- Customize the 404 error page Nginx (Server)

- Binary search and modification (Programming)

- Learn to read the source code of vmstat (Linux)

- Hibernate Search and Lucene quick introduction (Linux)

- Tip: Use Cryptsetup U disk encryption (Linux)

- Physical structure and process disk IO (Linux)

- Linux crontab (Linux)

- Linux common network tools: batch scanning of nmap hosting service (Linux)

- Confrontation dragged Library - Web front-end encryption slow (Linux)

- General Linux interface server parameter tuning (Server)

- Get basic information about Linux server script (Server)

- Linux disk management practices (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:      
 
- Linux Command study manual - GPG command (Linux)
- Linux regex sed detailing (Linux)
- Linux Task Scheduler (at, crontab) (Linux)
- Linux System Getting Started Tutorial: Installing Brother printer in Linux (Linux)
- Docker Build a Java compiler environment (Linux)
- Install Oracle JDK 8 and JVM class loading mechanism in Linux (Linux)
- Linux Command Tutorial: Ubuntu apt-get command (Linux)
- Alien Magic: RPM and DEB Mutual Convert (Linux)
- RHEL 7.1 compile and install Ganglia 3.7.1 (Server)
- Hadoop 2.2.0 installation development environment (standalone pseudo-distributed mode) (Server)
- How to install or upgrade to the Linux kernel in Ubuntu 4.2 (Linux)
- The difference between free command displays the buffers and cache (Linux)
- SSH keys using login and password to log prohibited practice (Linux)
- MongoDB common optimization settings in Linux (Database)
- Android View event delivery (Programming)
- CentOS Linux firewall configuration and Close (Linux)
- Use Vagrant up a local development environment tutorials (Server)
- Fedora 22 installation and configuration optimization (Linux)
- Using C ++ Container Templates in Shared Memory (Programming)
- Linux System Administrator Network Security Experience (Linux)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.