Remember MySQL index optimization

The two tables are the primary (check_drawings) from (check_drawings_img) relationship.

Check_drawings, Main table data 3,591.

SELECT COUNT (* from Check_drawings;--3591

CHECK_DRAWINGS_IMG, from table data 107,203, the amount of data is not large, and the primary key ID of the primary table is associated with check_drawings_id from the table.

SELECT COUNT (*) from check_drawings_img; --107203

An existing sq is as follows, according to EXHIBITION_ID query the last uploaded data under the main table

SELECTCDI.*, cd.booth_id fromCheck_drawings_img CDI, check_drawings CDWHEREcdi.check_drawings_id=cd.id andcd.exhibition_id= 'c480ffc7aed24789b025397c5b66ce88'    andCdi.upload_time=   (SELECT     MAX(cdi2.upload_time) fromcheck_drawings_img CdI2WHEREcdi2.check_drawings_id=Cd.id);

The execution time reaches about 14s, obviously cannot receive.

The execution plan information via MySQL explain is as follows:

Id

Select identifier, which is the select query serial number.

Select_type

Simple it represents a straightforward select with no union and subquery

Primary the outermost select, in the statement with the subquery, the outermost select query is primary

Dependent_subquery you need to pay special attention when you see "dependent subquery" in the Select_type field in the SQL execution plan.

Type

All: Full table scan

Index: Scan by index order, first read the index, then read the actual row, the result is a full table scan, the main advantage is to avoid sorting. Because the index is well-lined.

Range: Scan in the form of a range.

Ref: Non-unique index access (normal index only)

Eq_ref: Using a unique index lookup (primary key or unique index)

Const: Constant Query

Workaround one increases the index:

1, increase the index, we external inspection check_drawings_img.check_drawings_id increase index as follows:

  ALTER TABLE ADD INDEX check_drawings_id_idx (check_drawings_id);

Again, 0.078s is about 170 times faster than before:

Take a look at the execution plan:

Remember MySQL index optimization