MYSQL indexing requires attention to the following details
- 2020-05-15 02:25:06
- OfStack
1. Index timing: if a field in a table appears in select, filter, and sort conditions, it is worthwhile to index that field.
2. For the fuzzy query of like '%xxx', the normal index cannot be satisfied, so the full-text index should be established.
3. For those with more than one condition, such as: "... where a = xxx and b = yyy ", "... where a=xxx order by b","... where a=xxx group by b". Composite indexes are required. However, composite indexes can only be used if the "leftmost prefix" is satisfied in the SQL statement. And composite indexes have some side effects, such as the index size may be larger than the data itself, because composite indexes have more composite entries. So in the actual application, to tailor, the use of slow query analysis tool analysis.
4. Turn on the index cache and search the index directly in memory instead of on disk.
5. Indexing comes at a cost. When the update and delete statements are executed, the index will be updated and more time will be spent. You can use the mysqlreport report to see what percentage of each statement is select, update, delete, insert, replace.
2. For the fuzzy query of like '%xxx', the normal index cannot be satisfied, so the full-text index should be established.
3. For those with more than one condition, such as: "... where a = xxx and b = yyy ", "... where a=xxx order by b","... where a=xxx group by b". Composite indexes are required. However, composite indexes can only be used if the "leftmost prefix" is satisfied in the SQL statement. And composite indexes have some side effects, such as the index size may be larger than the data itself, because composite indexes have more composite entries. So in the actual application, to tailor, the use of slow query analysis tool analysis.
4. Turn on the index cache and search the index directly in memory instead of on disk.
5. Indexing comes at a cost. When the update and delete statements are executed, the index will be updated and more time will be spent. You can use the mysqlreport report to see what percentage of each statement is select, update, delete, insert, replace.