MySQL Optimization tips

Defragmentation:

MySQL data is stored sequentially on disk, if the data table has frequent update changes, then the data will form a lot of fragments, slow speed and unfavorable to the index;

There are two ways of optimizing fragmentation:

ALTER TABLE user engine InnoDB; in fact, the user this table was originally InnoDB, this sentence does not seem to make any sense, but MySQL will be re-structured data

Optimize table user; can also be repaired;

Fragmentation optimization is a matter of CPU and memory, preferably performed at night;

Unconventional min Max optimization

Select min (age) from table;

Regardless of how much data the table has, this statement is very fast, because MySQL is stored within the relevant values, through the explain can be seen by MySQL has been optimized;

Change the statement a little bit;

Select min (age) from table where Status=1;

Viewing by explain is a full table scan;

MySQL to a row of the check out to see if the status is 1, if it is, that compared to the last age to look at the small, a bit like bubble sort;

There is a special optimization method, in the context of age index;

Select min from table use index (age) where Status=1 limit 1;

Force MySQL to use the age index because the index is ordered, so the first one is the minimum value, and Max has the same

Count non-conventional optimizations

Select COUNT (*) from table;

By explain you can see that MySQL has been optimized to the solid level; very fast.

But:

Select COUNT (*) from table where id>100;

The speed drops rapidly, explain has seen the use of full-table scan, starting from 100 to scan the full table

However, this statement is fast and only uses the first 100 scans:

Select COUNT (*) from table where id<100;

So the optimization method is to use mathematical addition and subtraction:

(SELECT COUNT (*) from table)-(SELECT COUNT (*) from table where id<100)

Union optimization:

When connecting query results, use union all as necessary, because the union all does not filter data, high efficiency, and the Union to filter the data, the cost is great;

MySQL Optimization tips