Note the following details when creating an index for MYSQL: If a field in the table appears in the select, filter, and sort conditions, it is worthwhile to create an index for this field.
2. for fuzzy queries like '% xxx', normal indexes cannot be satisfied, and full-text indexes must be created.
3. if there are multiple conditions, such :"... where a = xxx and B = yyy ","... where a = xxx order by B ","... where a = xxx group by B ". A composite index is required. However, the composite index can only be used when the SQL statement meets the "leftmost prefix. In addition, composite indexes have some side effects. for example, the index size may be larger than the data because the composite indexes have many composite entries. Therefore, in practical applications, we need to customize it and use the slow query analysis tool for analysis.
4. enable the index cache to directly search for indexes in the memory and no more disks are needed.
5. index creation is costly. when the update and delete statements are executed, the index update will take more time. You can use the mysqlreport to understand the percentage of select, update, delete, insert, and replace statements. BitsCN.com
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
