I have some questions about mysql indexes. I have consulted a lot of information, but the information is very vague. There are several types of indexes and I don’t know how to add them:
1. How to add groupby indexes? Add an index alone or add it together with search conditions?
2. How to add a query index with LEFT JOIN, for example
SELECT a.id,FROM a
LEFT JOIN b
ON a.id = b.aid
WHERE a.year = 1 AND b.month = 2
GROUP BY b.a
ORDER BY a DESC
3.Like there
SELECT a.id,SUM(a.q+a.w) as day_pv FROM a
ORDER BY day_pv DESCHow to add such an index?
Things about indexing and mysql statement optimization are very weak.
Or if there are any related articles or books you can recommend
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
Summary of mysql composite index and ordinary index
What about books: High-Performance MySQL (Third Edition) Indexing Part, the one translated by Taobao guys
Building indexes is mainly based on the query conditions of the table. In principle, indexes are created on fields with high data selectivity, such as person IDs, names, etc. Based on such conditions, the query result set can be greatly reduced.
High-performance mysql third edition, Chapter 5, specifically explains the creation of high-performance indexes. The indexes are mainly established for different query fields of the table, in order to shorten query time and improve performance
Generally, fields used for sorting need to be added with B-tree indexes, where fields need to be indexed, multiple conditions need to be combined with indexes, and the index order must match the conditions