Database query Index (single index and composite index) __ Database

When a query for an SQL statement involves multiple fields, this time index each field, and the database can only use one of the indexes, it is better to use a composite index at this time. This is why.

This is because: Full table scan/using only one index compared to the speed of analysis of two index binary trees more time-consuming, so in most cases, the database is an index.
As this statement:

Select COUNT (1) from table1 where column1 = 1 and column2 = ' foo ' and column3 = ' Bar '
Let's imagine when the database has n indexes and the query uses them separately:
The query optimizer (that is, the thing that generates the execution plan with vernacular) needs to do n times the main binary tree lookup [where the main binary tree means the outermost index node], the search process here is probably as follows:
Find the first column1 the main binary tree equals 1, and then go to the second column2 the main binary tree to find out the value of Foo and the current line of coumn1 must equal 1, Finally go to column main binary tree to find the value of bar and Column1 must equal 1 and column2 must be equal to Foo.
If such a process is executed by the query optimizer, even if you don't die, the query optimizer can't wait to do all of the above, greedy algorithms (nearest neighbor algorithm) do not allow this to happen, so when you encounter the following statement, the database uses only the index of the first filtered column (Column1) , it will be directly to the table scan.

Select COUNT (1) from table1 where column1 = 1 and column2 = ' foo ' and column3 = ' Bar '

So instead of being a database that only supports one query statement using only one index, it's better to say that N-independent indexes use a single statement at the same time slower than using only one index.
So the best recommendation, as in the previous section, is to use the COLUMN1,COLUMN2,COLUMN3 index, a federated index that can take advantage of the B+TREE structure:
A main binary tree (column=1), query to Column=1 node based on the current node for two-level two-tree Column2=foo query, two-level two fork tree query to Column2=foo, to three-level two fork tree Column3=bar find.