Shulou(Shulou.com)06/01 Report--

Teacher Tian, statistics of the examination questions of Le Xue Medical examination

There are 50w records, and statistics take more than 20 minutes.

After optimizing the sql and indexing, the response only takes 0.1 second.

SHOW INDEX FROM `exam_question_ record`

ALTER TABLE `exam_question_ record` ADD INDEX index_qid_status (`qst_ id`, `status`)

View the index SHOW INDEX FROM `exam_question_ record`

1. Add PRIMARY KEY (primary key index)

Mysql > ALTER TABLE `table_ name` ADD PRIMARY KEY (`column`)

two。 Add UNIQUE (unique index)

Mysql > ALTER TABLE `table_ name` ADD UNIQUE (`column`)

3. Add INDEX (normal index)

Mysql > ALTER TABLE `table_ name` ADD INDEX index_name (`column`)

4. Add FULLTEXT (full-text index)

Mysql > ALTER TABLE `table_ name` ADD FULLTEXT (`column`)

5. Add multi-column index

Mysql > ALTER TABLE `table_ name` ADD INDEX index_name (`column1`, `column2`, column3`)

1. MySQL: the index is saved in B-tree format

The Memory storage engine can choose either Hash or BTree indexes, and Hash indexes can only be used for equation comparisons of = or.

1. General index: create index on Tablename (list of columns)

Alter table TableName add index (list of columns)

Create table TableName ([...], index [IndexName] (list of columns)

2. Uniqueness index: create unique index

Alter... Add unique

Primary key: a unique index that must be specified as primary key

3. Full-text indexing: full-text indexing and full-text retrieval are supported since version 3.23.23, FULLTEXT

Can be created on a column of type char, varchar, or text.

4. Single-column index and multi-column index:

The query effect of multiple single-column indexes is different from that of single multi-column indexes because:

When executing a query, MySQL can use only one index and selects the most restrictive index from multiple indexes.

5. Leftmost prefix (Leftmost Prefixing): multi-column index, for example: fname_lname_age index, the following search criteria MySQL will be used

Fname_lname_age index: firstname,lastname,age;firstname,lastname;firstname, which will not be used otherwise.

Second, according to the sql query statement to determine which type of index to create and how to optimize the query

Select the index column:

a. During performance tuning, choosing which column to create an index on is one of the most important steps. The main things that can be considered for using indexes are

There are two types of columns: columns that appear in the where clause and columns that appear in the join clause.

b. Considering the distribution of values in the column, the larger the cardinality of the column of the index, the better the effect of the index.

c. Using short indexes, if you index string columns, you should specify a prefix length, which can save a lot of index space and improve query speed.

d. Use the leftmost prefix

e. Don't over-index, just keep the index you need. Each additional index takes up extra disk space and degrades the performance of write operations.

When you modify the contents of a table, the index must be updated, and sometimes it may need to be refactored, so the more indexes, the longer it takes.

MySQL uses the index only on the following operator: =, between,in

And sometimes like (in cases where it doesn't start with the wildcard% or _).

Mysql index classification

In database tables, indexing fields can greatly improve the query speed. By making good use of these indexes, you can make MySQL query and run more efficiently. Indexing is the key to fast search. The establishment of MySQL index is very important for the efficient operation of MySQL. Here are several common MySQL index types.

1. General index

This is the most basic type of index, and it has no restrictions such as uniqueness. A normal index can be created in the following ways:

(1) create an index, such as the name of the CREATE INDEX index ON tablename (column name 1, column name 2.)

(2) modify the table, such as the name of the ALTER TABLE tablename ADD INDEX index (column name 1, column name 2.)

(3) specify the index when creating the table, such as CREATE TABLE tablename ([...], the name of the INDEX index (column name 1, column name).

2.))

2. Unique index

This index is basically the same as the previous "normal index", but there is one difference: all values of the index column can only appear once, that is, they must be unique. Uniqueness indexes can be created in the following ways:

(1) create an index, such as the name of the CREATE UNIQUE INDEX index ON tablename (list of columns)

(2) modify the table, such as the name of the ALTER TABLE tablename ADD UNIQUE index (list of columns)

(3) specify the index when creating the table, such as CREATE TABLE tablename ([...], the name of the UNIQUE index (column of the column)

Table))

ALTER TABLE `timetable`

ADD UNIQUE index_user_date_hour (`userId`, `date`, `dateHour`)

3. Primary key

The primary key is a unique index, but it must be specified as "PRIMARY KEY". If you have ever used AUTO_INCREMENT-type columns, you may already be familiar with concepts such as primary keys. The primary key is generally specified when the table is created, such as "CREATE TABLE tablename ([...], PRIMARY KEY (list of columns);". However, we can also add the primary key by modifying the table, such as "ALTER TABLE tablename ADD PRIMARY KEY (list of columns);". Each table can have only one primary key. (the primary key is equivalent to the aggregate index and is the fastest index to find)

4. Single-column index and multi-column index

An index can be a single-column index or a multi-column index.

(1) A single-column index is a commonly used index of column fields, a common index.

(2) A multi-column index is an index with multiple column fields.

Alter table student add index sy (name,age,score)

Index sy is a multi-column index, and a multi-column index is valid in the following situations:

Select * from student where name='jia' and age > = '12' / / where condition contains the first column fields of the index and

Second field

Select * from student where name='jia' / / where condition contains only the first column of fields

Select * from student where name='jia' and score

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.