Small ant learns MySQL performance optimization (4)--sql and index optimization--specific optimization methods

Source: Internet
Author: User

Optimization of Max ()

For example: Explain select Max (' Payment_date ') from payment; The number of rows that can be viewed to row is very much, which means that the entire table is scanned.

Add an index to payment_date.

CREATE INDEX Idx_payment on payment (' payment_date ');

Execute SQL statement explain select MAX (' Payment_date ') from payment order by Payment_date; You can see that rows is null.

Extra:select tables optinized away;

Does not scan the entire table, just take out through the index.

Indexes are arranged sequentially, so it is very clear how the last Payment_date value is to be known by the statistical information of the index. Therefore, the operation of the table is not necessary, which greatly increases the efficiency of the SQL execution, while minimizing the IO operation, so that the execution efficiency is basically constant, no matter how large the data volume is.

Idea: the query for Max () can be indexed to optimize it.

The difference between count (*) and COUNT (num)

If NUM has a behavior of null,count (num), it is not included. The Count (*) is included. That's why there are times when differences occur.

For example:

ID num

1 1

2 2

3 null

COUNT (*) =3 but count (ID) =2

Optimization of sub-queries

Sub-query with connection mode to pay attention to whether it is a 1-to-many relationship, if so, data results will be duplicated, need to use distinct to heavy.

Limit () optimization

Optimization Step 1: Use the indexed column or the primary key column for the order by operation. The truth is the same as Max ().

Optimization Step 2: Record the last returned primary key and use primary key filtering at the next query.

For example: Select File_id,description from file where file_id >55 and file_id <= the order by film_id limit 1, 5;

The disadvantage of this approach is that in case the primary key is missing, it is not necessarily 5.

d===== ( ̄▽ ̄*) B Center Optimization idea: Avoid scanning too many records when the data volume is large.

Small ant learns MySQL performance optimization (4)--sql and index optimization--specific optimization methods

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.