MySQL How to view SQL statement execution time and efficiency

View Execution Time

1 show Profiles;
2 show variables; see if the profiling is on state;
3 If it is off, set profiling = 1;
4 execute its own SQL statement;
5 show profiles; the execution time of the SQL statement can be found;

See how many rows were manipulated

Add explain in front of the SQL statement;

explain select * from event;  +—-+————-+——-+——+—————+——+———+——+——+——-+  | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |  +—-+————-+——-+——+—————+——+———+——+——+——-+  | 1 | SIMPLE | event | ALL | NULL | NULL | NULL | NULL | 13 | | +—-+————-+——-+——+—————+——+———+——+——+——-+ 1 row in set (0.00 sec) 

The meaning of each property

Id

Serial number of the select query

Select_type

The type of select query is mainly the difference between common queries and complex queries such as federated queries and subqueries.

Table

The table that is referenced by the output row.

Type

The type used by the union query.

Type shows the type of access, which is an important indicator, and the resulting values from good to bad are:

System > Const > EQ_REF > Ref > Fulltext > Ref_or_null > Index_merge > Unique_subquery > Index_sub Query > Range > Index > All

In general, it is best to ensure that the query reaches at least the range level, preferably ref.

Possible_keys

Indicates which index MySQL can use to find rows in the table. If it is empty, there is no index associated with it. To improve performance, you can examine where clauses to see if some fields are referenced, or check that the fields are not appropriate for the index.

Key

Displays the keys that MySQL actually decides to use. If no index is selected, the key is null.

Key_len

Displays the key lengths that MySQL decides to use. If the key is null, the length is null. Documentation tips pay particular attention to this value to derive a multi-primary key in what part of MySQL is actually used.

Ref

Shows which field or constant is used together with the key.

Rows

This number indicates how much data MySQL will traverse to find and is inaccurate on InnoDB.

Extra

If it is only index, this means that information is retrieved only from the information in the index tree, which is faster than scanning the entire table.

If it is a where used, the where limit is used.

If it is impossible where means no where, it is generally not found out what.

MySQL How to view SQL statement execution time and efficiency