Using Where; Using Index; Using Temporary; Using Filesort Mysql Explain Plan

Therein article we will spill about query optimization with the cente the queries with GROUP BY and ORDER BY. We will start with the radical concepts (Indexes, Explain project in MySQL, etc) and then leave blab ou about the forward-looking query optimisation techniques. We will cover "loose index scan" and "snug index glance over" optimizations and show the benchmark results.

Indexes

In this article we leave cente the b-tree indexes only (InnoDB and MyISAM only support B-tree indexes). Visualize 1 illustrates a basic b-tree indicator implementation.

B-tree diagram supports both "equality" (where id = 1) and range (where date > '2013-01-01' and date < '2013-07-01') search. Figures 2 and 3 show examples of these, illustrated.

Figure 2 is of an equality look for with primary or unique key: blue-ribbon select * from table where id = 12 . In this scenario MySQL will equal capable to scan through the Tree and go immediately to 1 leaf and then finish. That is usually the fastest index scan operation.

In InnoDB primary feather key searches is even quicker As InnoDB "clusters" records around main key.

Range: select * from table where Idaho in (6, 12, 18)

In this scenario MySQL will need to rake thru the tree and visit many leafs/nodes:

Table for the tests

I will use a couple of tables for the tests. The first hold over we bequeath use to instance is explained on a lower floor. This shelve is the part of MySQL "reality" test db and stern comprise downloaded from dev.mysql.com .

            CREATE Mesa City (     ID int(11) NOT NULL AUTO_INCREMENT,     Name char(35) NOT NULL DEFAULT '',     CountryCode char(3) NOT NULL DEFAULT '',     District char(20) NOT Cipher DEFAULT '',     Population int(11) NOT NULL DEFAULT '0',     PRIMARY KEY (ID),     Headstone CountryCode (CountryCode) ) Locomotive=InnoDB;

Explain contrive

The briny way to "profile" a query with MySQL is to use "explain" . The output below shows an example. As we can see from the explicate, MySQL does not use any indexes (key: Nil) and wish make to perform a full table scan.

mysql> EXPLAIN select * from Urban center where Name = 'London'G *************************** 1. run-in            id: 1   select_type: SIMPLE         table: City          type: ALL possible_keys: NULL           key: NULL       key_len: Invalid           ref: NULL          rows: 4079         Extra: Using where

In that case we rear end add an index to restrict the number of rows:

            mysql> interpolate table City add tonality (Name); Query Alright, 4079 rows hokey (0.02 sec) Records: 4079  Duplicates: 0  Warnings: 0

The new explain shows that MySQL will use an index:

mysql> excuse choice * from City where Name = 'London'G *********************** 1. row ***************************            id: 1   select_type: Elongate         table: City          type: referee possible_keys: Name           key: Name       key_len: 35           ref: const          rows: 1         Extra: Exploitation where

Index usages

MySQL will choose only 1 index per query (and per table if the question joins multiple tables). In some cases MySQL bottom also intersect indexes, however we will not cover it in this article. MySQL uses index statistics to make a decision about the best possible index.

Combined Indexes

Combined indexes are very important for MySQL query optimizations. MySQL can use leftmost part of any index. For example, if we let this index number:

            Comb(CountryCode, District, Population)

Then MySQL can use:

            CountryCode only split up CountryCode + Zone CountryCode + District + Population

From the explicate plan in to a lower place we can understand which part(s) will be used:

            mysql> explain select * from Metropolis        where CountryCode = 'US'G ********************** 1. row ******************         put over: City          character: ref possible_keys: comb           key: comb       key_len: 3           ref: const          rows: 273

Note the key_len part – it shows: 3. This is the number of bytes used from our index. As the CountryCode athletic field is declared as scorch(3), that substance that MySQL will use the first field from our combined index.

Similarly, MySQL can use 2 leftmost fields surgery all 3 fields from the index. In this model, the 2 leftmost fields are misused:

            mysql> explain select * from City  where CountryCode = 'USA' and District = 'California'G ********************** 1. dustup ******************         table: Urban center          type: referee possible_keys: comb           of import: comb       key_len: 23           ref: const,const          rows: 68

So MySQL will use 2 first off William Claude Dukenfield from the comb key:

CountryCode = 3 chars
Dominion = 20 chars
Total = 23

Whereas therein one, all 3 Fields are used from the index:

mysql> explicate select * from City  where CountryCode = 'USA' and Territorial dominion = 'California' and population > 10000G ********************** 1. row ******************         set back: City          type: range possible_keys: comb           key: comb       key_len: 27           ref: NULL          rows: 68

CountryCode = 3 chars/bytes
District = 20 chars/bytes
Universe = 4 bytes (INT)
Total = 27

Notwithstandin, if the query does not have the first left part of an index, MySQL will not be able to use it:

mysql> explain prime * from City where   District = 'California' and universe > 10000G ********************** 1. row ******************  hold over: City         type: ALL possible_keys: NULL          key: NULL      key_len: Zipp          ref: NULL         rows: 3868

As we do not have the CountryCode in the where clause, MySQL will not equal able to use our "comb" index.

Covered indicator

The covered index is an index that covers every fields in the enquiry. For example, for this query:

blue-ribbon name from Metropolis  where CountryCode = 'USA' and District = 'Alaska'  and population > 10000

the following index volition equal a "covered" index:

cov1(CountryCode, District, population, advert)

The above index uses whol fields in the query particularly order:

Where voice
Mathematical group By/Order (not used now)
Select part (Here: key)

Here is an good example:

mysql> explain select name from Metropolis  where CountryCode = 'USA' and District = 'Alaska' and universe > 10000G *************************** 1. row ***********         table: City          type: range possible_keys: cov1           key: cov1       key_len: 27           ref: NULL          rows: 1         Special: Using where; Using index

"Using index number" in the supererogatory field of the explain yield substance that MySQL will utilise our crusted index. That also way that MySQL wish employment an index only to satisfy the query: completely the information that MySQL needs is in the index. That is usually much faster, particularly if we have a large textual matter or blob fields in the table.

Order of the fields in index

The order of the fields in the index finger is very important. The mode b-Sir Herbert Beerbohm Tree kit and boodle, IT is to a greater extent beneficial to have a athletic field which will be used for "equality" comparison first and the W. C. Fields with "range" (much and less than comparison) second base.

For instance, take the favorable interrogation:

select * from Urban center where zone = 'California' and universe > 30000

The best Exponent will be on (district, population), in this particular proposition gild.

(territorial dominion, population) indicant [Figure 4]: In that case MySQL will Be healthy to go "directly" (via exponent scan) to the counterbalance district ("CA") and do a range scan by population. Altogether other nodes for the "district" field (other U.S. states in this example) will non represent scanned.

(population, district) index [Figure 5]: Therein example, MySQL leave have to act up a "range" scan for the population and for each indicant record it testament have to check for the correct territorial dominion, which will be slower.

Complex Slow Queries

In this article we will be concentrating on 2 stellar interrogation types:

Queries with "group by"
Queries with "dictate aside"

Those queries are usually the slowest ones. We will demo how to optimize those queries and decrease the query answer sentence arsenic fit as the application performance in comprehensive.

Group by example

Let's look at this simple good example, a question of how many cities on that point are in each country.

mysql> explicate select name from City  where CountryCode = 'United States' and District = 'Alaska' and population > 10000G *************************** 1. row ***********         table: City          type: range possible_keys: cov1           key: cov1       key_len: 27           ref: NULL          rows: 1         Unscheduled: Using where; Using forefinger

As we dismiss see the yield above, MySQL does not use any indexes (no proper indexes are available), but IT also shows "Using temporary; Using filesort". MySQL will need to make over a temporary table to satisfy the "Chemical group by" clause if there is no appropriate index finger (You fanny find more information about the temporary tables here). However, MySQL fanny use a combined power to gratify the "group by" article and stave off creating pro tempore table.

Group by and covered index

To illustrate the "chemical group away" queries I leave use the following defer:

CREATE TABLE ontime_2012 (     YearD int(11) DEFAULT Zip,     MonthD tinyint(4) DEFAULT NULL,     DayofMonth tinyint(4) DEFAULT Invalid,     DayOfWeek tinyint(4) DEFAULT Invalid,     Carrier char(2) DEFAULT NULL,     Origin char(5) DEFAULT NULL,     DepDelayMinutes int(11) DEFAULT Naught,     ... ) ENGINE=InnoDB DEFAULT CHARSET=latin1

The table contains freely available airline business performance statistics. The table is 6 million rows and about 2GB in size.

From this information we require to:
Find maximum time lag for flights on Billy Sunday
Group by airline

Our example query is:

select easy lay(DepDelayMinutes),  carrier, dayofweek  from ontime_2012  where dayofweek = 7  group by Carrier

The explain architectural plan is:

...         type: ALL possible_keys: NULL          key: NULL      key_len: Zip          ref: NULL         rows: 4833086        Extra: Exploitation where; Using temporary; Victimisation filesort

As we can see, MySQL does not utilization any index and have to scan ~4M rows. To boot, IT will have to create a large temporary table.

If we will create an power happening "dayofweek" it will only filter out some rows and MySQL will still need to produce a temporary table:

mysql> explain select name from Metropolis  where CountryCode = 'USA' and District = 'AK' and population > 10000G *************************** 1. row ***********         set back: City          type: range possible_keys: cov1           key: cov1       key_len: 27           ref: NULL          rows: 1         Extra: Using where; Using indicator

However, we potty produce a covered index happening (dayofweek, Newsboy, DepDelayMinutes) in this particular order. In this case MySQL volition be able to use this index and avoid creating a temporary defer:

mysql> alter put over ontime_2012  add key covered(dayofweek, Carrier, DepDelayMinutes);  excuse select max(DepDelayMinutes), Common carrier, dayofweek from ontime_2012 where dayofweek =7 group by CarrierG ...              possible_keys: DayOfWeek,covered          fundamental: covered      key_len: 2          ref: const         rows: 905138        Extra: Victimization where; Using indicant

As we stool see from the explain, MySQL volition use our forefinger and leave avoid creating a temporary table. This is the fastest possible solution.

Banknote that MySQL will also be able-bodied to use non-covered index on (dayofweek, Carrier) and avoid creating a temporary table. However, awninged index will be faster as MySQL will represent able to fulfill the uninjured query by just reading the index finger.

Group by and a range scan

The covered exponent works swell if we have a "const" (where dayofweek=N). Nevertheless, MySQL will non be able to use an index and avoid a filesort if we have a "order" scan in the where clause. Here's an example:

mysql> explain select easy lay(DepDelayMinutes), Carrier, dayofweek from ontime_2012  where dayofweek > 5 group by Carrier, dayofweekG ...          type: range possible_keys: covered          key: overgrown      key_len: 2          referee: NULL         rows: 2441781        Extra: Using where; Exploitation indicant; Using transient; Victimization filesort

MySQL will nonetheless feature to create a temporary hold over. To fix that we can consumption a simple trick and rewrite the query into 2 parts with UNION:

(select max(DepDelayMinutes), Carrier, dayofweek  from ontime_2012  where dayofweek = 6  group aside Carrier, dayofweek) union (select max(DepDelayMinutes), Carrier, dayofweek  from ontime_2012  where dayofweek = 7  radical away Carrier, dayofweek)

For each of the 2 queries in the uniting MySQL will be able to use an index instead of creating a irregular table, as shown in the explicate be after infra:

*************************** 1. row ***************************        table: ontime_2012          key: covered ...        Spear carrier: Using where; Using index *************************** 2. rowing ***************************        table: ontime_2012          key: covered ...        Extra: Using where; Using index *************************** 3. row ***************************           id: Nada  select_type: Sexual unio RESULT        table: <union1,2>         type: Complete possible_keys: NULL          key: NULL      key_len: Nothing          ref: Goose egg         rows: NULL        Extra: Using temporary

As we bum see, MySQL uses our covered index number for apiece of the 2 queries. It testament still have to create a short-lived table to merge the results, however, it wish likely be much smaller irregular table as it leave only need to store theresultsets of 2 queries.

Chemical group by and a loose index CAT scan

Loose index scan is another MySQL algorithm to optimize Group By queries. Loose indicator skim considers only a fraction of the keys in an index and is identical fast. The following limitations apply:

The query is over a single table.
The GROUP BY names only columns that form a left prefix of the index and no other columns.
The only totality functions used in the select listing (if any) are MIN() and MAX(), same column

More information can be saved in MySQL documentation on Loose Index Run down.

For loose index scan to work we will call for to create an additional index finger and start with columns in the "Group by" clause and so all fields in the "where" clause (order of the fields in the index matters!). For example, for our query:

select max(DepDelayMinutes) as ddm, Toter, dayofweek from ontime_2012  where dayofweek = 5  group aside Carrier, dayofweek

We will need to create this index number:

KEY loose_index_scan (Carrier,DayOfWeek,DepDelayMinutes)

Tone that loose index number scan is only if a name of the index, it can exist any bring up:

mysql> explain pick out max(DepDelayMinutes) every bit ddm, Carrier, dayofweek from ontime_2012  where dayofweek = 5  group by Carrier, dayofweek         table: ontime_2012         type: range possible_keys: canopied          key: loose_index_scan      key_len: 5          ref: NULL         rows: 201        Surplus: Exploitation where; Using index for group-by

"Using index for group-aside" in the where article means that MySQL will use the loose index scan. Loose power scan is rattling fast as it solely scans a fraction of the key. It will also work with the vagabon scan:

mysql> excuse select max(DepDelayMinutes) as ddm, Carrier, dayofweek from ontime_2012  where dayofweek > 5 mathematical group aside Carrier, dayofweek;  table: ontime_2012         type: cooking stove possible_keys: crusted          tonality: loose_index_scan      key_len: 5          referee: Nothing         rows: 213        Extra: Using where; Victimisation indicant for group-by;

Benchmark

We have performed a benchmark to equate enquiry speed with a temporary tabular array, a tight power scan (covered index) and a loose index scan. The table is 6 million rows and around 2GB in size.

As we dismiss see, loose index scan indicant shows the C. H. Best performance. Unluckily, loose index rake only works with 2 aggregate functions, min and max for the group by. "AVG" + group by does not work with loose index rake. American Samoa we can see to it below, MySQL will use a covered index (not loose index scan exponent) and, because we have a range in the where clause (dayofweek > 5), it will have to make over a temporary defer.

mysql> explain select avg(DepDelayMinutes) as ddm, Aircraft carrier, dayofweek from ontime_2012 where dayofweek >5  chemical group aside Carrier, dayofweek G          table: ontime_2012         type: range          headstone: moss-grown      key_len: 2          ref: NULL         rows: 2961617        Extra: Using where; Using index; Using temporary; Victimization fileso

Ordering BY and filesort

MySQL may have to perform a "filesort" operation when a query uses the "order by" clause. You can line up more information about "filesort" here.

The filesort operation on a lower floor is usually pretty slow operation (even if it does not need creating a file on phonograph record), especially if MySQL will have to sort a lots of rows:

table: City          case: Altogether possible_keys: NULL           significant: NULL       key_len: NULL           ref: NULL          rows: 4079         Extra: Using where; Using filesort

To optimize this query we can role a rolled into one index:

mysql> alter table Urban center  sum key my_sort2 (CountryCode, population);  mysql> explain prize district, epithet, population from City where CountryCode = 'USA' club by population desc limit 10G         table: Metropolis         type: referee          key: my_sort2      key_len: 3          ref: const         rows: 207        Extra: Exploitation where

MySQL was able to use up our combined index to avert sorting: as the index is sorted, MySQL was able to read the index leafs in the correct order.

Classification and limit

If we have a "LIMIT" clause in our query (and the limit is relatively small (i.e. LIMIT 10 operating theatre LIMIT 100) compared to the amount of rows in the table), MySQL can avoid exploitation a filesort and can utilize forefinger instead.

Here's an example:

mysql> castrate table ontime_2012 add cardinal (DepDelayMinutes);

We can create an indicator on DepDelayMinutes fields only and run the explain below (note the query with LIMIT 10):

mysql> explain select * from ontime_2012  where dayofweek in (6,7) regularize past DepDelayMinutes desc limit 10G          type: index number possible_keys: DayOfWeek,cloaked          key: DepDelayMinutes      key_len: 5          ref: NULL         rows: 24        Extra: Victimisation where

A closer flavor at query optimization with a focus on queries with Aggroup BY and ORDER Past, from Percona consultant Alexander Rubin. This clause was originally published on webandphp.com.

Using Where; Using Index; Using Temporary; Using Filesort Mysql Explain Plan

Source: https://jaxenter.com/level-up-your-mysql-query-tuning-107279.html