As a PHP developer, the index of MySQL is an essential issue
For example, an SQL query:
SELECT * FROM Userstatic where the nick like "" and rip=3726854906;
Some of the questions that may be asked are:
1. If Nick is not an index and RIP is an index, will this condition order be problematic?
The problem is simple, and MySQL itself will judge the condition and index, so that the write can use the index, no problem.
2. If Nick and rip are indexes, is there a problem with writing?
We all know that a query can use only one index at a time, so how do you use the index?
Many of the answers on the web tell us that if two fields are indexed, first match the left field, that is, Nick, then the answer is obvious, estimating the number of rows that nick and rip might affect, and which field affects fewer rows, which is written on the left.
Is that what it is? First we set up both Nick and rip as indexes, using explain to look at:
In the key column, we can see the use of Nick as an index, there seems to be no problem, in a different order to try:
or using Nick as an index, not exactly what we were thinking.
What is it, I try to analyze, first of all we look at these two fields as a condition when the result of how many rows:
The number of rows that nick may affect is 7 rows (rows column is not an exact value)
The number of rows that rip can affect is 181 lines, and will that have anything to do with it? Let's look at a different value:
This rip can affect 1 rows, less than the number of rows that Nick might affect, and then look at the two conditions that are used:
The index used this time is RIP.
So can we conclude that when we write SQL statements, we don't care about the order of the conditions, and MySQL optimizes it when it's looking.
MySQL Learning index Order