Database Structure Design and how to maximize the use of indexes and database Indexes

Database Structure Design and how to maximize the use of indexes and database Indexes

For example, a message table

The structure is as follows: Id (auto-incrementing primary key) Content (Content) UserId (speaker Id) IsWonderful (whether it is a brilliant speech) Top (whether it is set to the Top) IsBarrage (whether it is a bullet screen)

When the table data in this structure is small, it is faster to set the top comments and bullet screens for frequent speeches.

However, when there are more than 0.3 million data records, you can find the data.

Find frequently spoken items in pieces of data

Time: 4.753 seconds

Although the data can be searched for 0.1 seconds after the index is added, the write speed of each index is slowed down. After the index is set to the top of the screen, it is not necessary to add the index. writing is very slow, especially in this case. when tables are frequently written, the design of this table is very problematic.

In this case, when creating a table, the structure is

Id Primary Key

Type 1-brilliant speech 2-top, 3-bullet Screen

ChatMessageId: Id of the message table

 

Next it's time to verify with facts.

Next let's take a look at the analysis

Perfect use of existing primary key indexes

 

It took only 0.0001 seconds to find the frequently spoken data in pieces of data, and no new indexes were added. How can I see this?

Do not add new indexes when designing databases in the future (unless there are few writes or changes). Try to use the built-in primary key index.