What is the function of the use index in the MySQL query statement?

Just use the in operator wrongly, the logic of in is "or", number in ('1','4','6','13') is equivalent to number='1' or number='4' or number='6' or number='13', and there is no 13 in your number (there is no 13 in 10 values), then it is equal to 13, and the filtering effect is equivalent to not adding this condition, so 3 records are found. According to your intentions, it seems that 0 records should be found, which will probably need to be written as the following command: select number from abc where number in ('1','4','6','13') and exists( select 1 from abc where number='1' ) and exists( select 1 from abc where number='4' ) and exists( select 1 from abc where number='6' ) and exists( select 1 from abc where number='13' )

Mine is hard. Very good, 2011 9 26 13:09:16

Both use index (the name of the index key) and force index (the name of the index key) force the MySQL optimizer to use the index to query.

This is in the sqlserver help.

use changes the database context to the specified database.

syntax use parameter database.

is the name of the database to which the user context is switching. The database name must conform to the rules for identifiers.

Switch to database.

use mydb

This is to set mydb as the current database.

Key is the key value, which is part of the theory of relational model, such as the primary key and the foreign key

key) for data integrity check and uniqueness constraints. For example, you can index any column of a table, so when the index column is in the where condition in the SQL statement, you can get fast data location and fast retrieval. As for unique

index, which is only one of the indexes, establishes a unique index to indicate that the data in this column is not repeatable, and guesses that MySQL is unique

indexes of the index type can be further optimized.

Therefore, when designing a table, the key should only be at the model level, and when query optimization is required, the relevant columns can be indexed.

In addition, in MySQL, MySQL has automatically created a unique index for a primary key column, so there is no need to create a duplicate index on it.

The unique value of key, which cannot be repeated, is used to determine a certain row.

Index is mainly used to improve the query and sorting speed, and there are also options that cannot be repeated.

key indicates the primary key, which is the unique identifier of a record.

index is an index, which is created to speed up the query.

Key and index are both indexes, and index is commonly used.

MySQL uses indexes in two ways after they are created:

1. The query optimizer of the database automatically determines whether to use the index;

2 Users can force the use of indexes when writing SQL statements.

The following describes the two types of indexes.

First, the index is used automatically. After receiving the query statement, the database checks the query conditions behind the where statement, checks the indexes on the table, and matches the indexes based on the query conditions.

The matching of query conditions and indexes includes the matching of query fields and index fields and the matching of query types and index types. The former is easy to understand, that is, the query condition must have an index on the attributes, and the latter means that the query condition must be able to use an index, for example, the b+ tree index can be used for equivalence judgment and range query, while the hash index can only be applied to equivalence judgment.

Generally speaking, if the number of records accessed through the index accounts for more than 15% of the number of records in the whole table, the index will not be used but the full table scan will be used, because the cost of using the index is greater. In most cases, using indexes will be more efficient.

At the discretion of the optimizer, the final decision is made on whether to use the index.

The second type, the mandatory use of indexes, is mainly implemented through SQL statements.

select * from table force index(pri) limit 2;(Enforce the use of primary key).

select * from table force index(ziduan1_index) limit 2;(Indexing is enforced.)"ziduan1_index")

select * from table force index(pri,ziduan1_index) limit 2;(Indexing is enforced.)"pri and ziduan1 index")

You can also prohibit the use of indexes.

select * from table ignore index(pri) limit 2;(Primary key is prohibited).

select * from table ignore index(ziduan1_index) limit 2;(Indexing is prohibited.)"ziduan1_index")

select * from table ignore index(pri,ziduan1_index) limit 2;(Indexing is prohibited.)"pri,ziduan1_index")

1。This is oracle syntax.

2。*+ index(slms tralog t slms tralog called idx) means that the slms tralog called idx index of the slms tralog t table is used in this query, and of course this index will be used in the where condition later.

To add, this is not called an index function, but a forced index.

sql="insert

intoinlib(indate,bookname,bookid,innum,,price,money)values(getdate(),'"&

bookname

bookid

innumpricemoney

innum,,price

Multiple commas between these two fields.

That's not the case, hehe.

You check to see if the data type is wrong.

Or put the SQL statement in the query analyzer and execute it.

See what the hints are wrong.

Syntax in Oracle to specify how the query optimizer is selected.

Your scanning method for the index of the meaning of this sentence;

+ index(tablename index) * to select the scanning method for the index.

The index value is the index name, not the column name.

+use hash(tablename a, tablename b)* Select hash join, large and small table joins are more efficient.

+all_rows*/

It is shown that the overhead-based optimization method is selected for the statement block, and the best throughput is obtained, so that the resource consumption is minimized.

+first_rows*/

It is shown that the cost-based optimization method is selected for the statement block, and the best response time is obtained, so that the resource consumption is minimized.

+full(table)*/

Indicates the method for selecting a global scan for the table.

index desc(tablename,index)* in descending order by index column, which is generally faster than direct order by index desc.

+index asc(tablename,index index)* Same as above, just in ascending order.

I haven't used MySQL before, and the above are just some of the commonly used ones in Oracle.

This is the syntax format for the new statement.

Indexes are implemented in the storage engine, so the indexes of each storage engine are not necessarily identical, and each storage engine does not necessarily support all index types.

Define the maximum number of indexes and the maximum index length per table based on the storage engine. All storage engines support at least 16 indexes per table, with a total index length of at least 256 bytes.

The Myisam and InnoDB storage engines only support btree indexes. The memory and HEAP storage engines can support hash and btree indexes.

A b-tree index is a way to access and find files (called records or key-values) in a database. The B-Tree algorithm reduces the intermediate process that goes through when locating the record, thereby speeding up access.