I have seen a lot of articles on mysql indexes that say that if a field has a small range of values (a large number of duplicate values), there is no need to establish an index.
but my actual test:
user_type_id field has an index:
then execution time:
1s when indexed
6s when there is no indexed reference
you can see that the query speed has improved significantly.
then why do you still say that?
index is not absolute, it depends on whether the index can reduce IO.
first of all, in your example, you can obviously use user_score_have_index_user_type_id_index to quickly locate the data of type=5, and the index is orderly, that is to say, you only need to locate the data of the first type=5, and you can read to the right along the pointer, which is much faster than the discrete reading of the whole table. In addition, for both id and type conditions, user_score_have_index_user_type_id_index already overrides the index. Brother, can the index name be shorter? )
it is often said that duplicate values are not suitable for indexing, taking into account the cost of returning to the table.
it's not that you don't need to add, it's that it's of little use. If you look at the specific scene and the scan of the table, and 1s and 6s themselves are not "significantly improved" or dozens of times, if it's not count, and get a few pieces of data, your index is useless. For such a large data, you can't get count, directly, you should store it in other places, and the database will fail after a few requests. You just happen to encounter the usefulness of count, but it doesn't work well in most cases
MySQL index principle and slow query optimization
talking about optimization without actual business is a hooligan.
if the above reply has already been mentioned, just add a
I ask you if a sentence is visited very frequently, but there are a lot of repetitions, do you add it?
Previous: How does hashmap ensure visibility?
Next: What language do you want to learn to play AI in your spare time?
Test database downloaded on github. Index: explain select: query the scope of the date field of the employees table if you want to query the recruits in a certain period of time, 1. Why interpret the results, using the type field or ALL? What doe...