A little learning of database indexing (pending update)

Tag: occupy the profile ROM run a problem: creat select

It is important to note that too many indexes can affect the speed of updates and insertions because it requires agreeing to update each index file.

For a table that needs to be updated and inserted frequently, it is not necessary to index the

For smaller tables, there is no need to create additional indexes

Indexing takes up disk space

The most important issues that are addressed by the index:

When the amount of data is very large, and the data does not need to be changed frequently, in order to speed up the query, the index is used

Can be tested to increase the query speed after indexing

1. Open Run time monitoring

Set profiling=1;

2. Query 10,000th data (name = ha-99999)

SELECT * FROM test where name = ' ha-99999 ';

3. Check the time of execution

Show Profiles;

4. Create an index for the name column of the table;

command to add an index to a field

Create index name on table name (field name);

CREATE INDEX Test_index on test (name);

5. Execute the query statement again

SELECT * FROM test where name = ' ha-99999 ';

6. Check the time of execution again

Show Profiles

A little learning of database indexing (pending update)