How to use index to optimize MySQL properly

In MySQL, because the use of limit this syntax, for paging query is very convenient, but also brought in the ranking of performance bottlenecks, reasonable use of the index, can greatly improve the running speed of MySQL.

Indexes are generally applied to fields used for cross table joins and sorting, and in practical applications, we tend to apply to the possibility of ordering multiple fields, and the following points need to be noted:

1, the primary key field does not need to re-establish the index

2, for the use of multiple fields by the sort of possible situation, to establish an index do not put the index in the same group, such as
Select * from xxx_table where xxx=1 order BY click Desc
Select * from xxx_table where xxx=1 order by postdate Desc
When this happens, you have to create a set of indexes for each click and postdate, otherwise the index has too many fields attached to it, and the index of the system is basically useless.

3, the frequently queried fields can be indexed.