The design of database structure and how to make maximum use of index

For example, a list of messages

Structure such as the Id (self-increment primary key) content UserId (speaker Id) iswonderful (whether it is a good speech) Top (whether it is pinned) isbarrage (whether it is a barrage)

When the table data of this structure is still young learn to speak on the top of the speaker and the barrage speed faster

But when data is more than 30多万条, finding data

Find frequent speeches in 37w data

spents 4.753 seconds

Although the index can be found after 0.1 seconds to find the data, but each additional index write speed slowed down a little behind, and also to find the top of the screen is not to be indexed so write very slow especially when the table is frequently written, it is more important that the design of this table has a lot of problems

At this time in the construction of a table structure is

ID PRIMARY Key

Type 1-Wonderful speeches 2-top, 3-barrage

Chatmessageid Message table ID

And then it's time to prove it.

Next we look at the analysis

The perfect use of the existing primary key index

It took only 0.0001 seconds to find regular speeches in 37w data, and no new indexes were added. How do you see this is thoughtful?

When you design your database later, don't think about adding new indexes (unless you write and change very rarely) try to take advantage of your own primary key index

The design of database structure and how to make maximum use of index