How to establish a reasonable MySQL index of an article table - Codes Helper - Programming Question Answer

How to establish a reasonable MySQL index of an article table

MySQL table with the following structure

CREATE TABLE `t_article` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `content` varchar(255) NOT NULL COMMENT "",
  `like_count` int(10) unsigned NOT NULL DEFAULT "0" COMMENT "",
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT="";

insert data:

INSERT INTO `t_article` (`id`, `content`, `like_count`) VALUES ("1", "aaa", "4");
INSERT INTO `t_article` (`id`, `content`, `like_count`) VALUES ("2", "rrrr", "53");
INSERT INTO `t_article` (`id`, `content`, `like_count`) VALUES ("3", "ttt", "7");
INSERT INTO `t_article` (`id`, `content`, `like_count`) VALUES ("4", "rree", "6");
INSERT INTO `t_article` (`id`, `content`, `like_count`) VALUES ("5", "rrr", "888");

the requirement is to query the list of articles, sorted from large to small according to the number of likes (like_count) field. The SQL statement is as follows:

SELECT id FROM t_article ORDER BY like_count DESC LIMIT 0,4;

how to build an index is reasonable. The reason for the problem is that after reading many big articles, you will say that the index field should not be updated frequently, but the like_count field must be updated frequently.

there is another derivative problem. The following SQL performs an analysis of whether there is something wrong with type as index. This SQL does not use where conditions and sort fields, but still performs a full table scan. How to optimize it

SELECT id FROM t_article LIMIT 0,4;

EXPLAIN analysis

Golang php java mysql-Index mysql

Jan.08,2022

as to whether the index field can be modified frequently, it also depends on the amount of data. If your amount of data is not very large (for example, only hundreds of thousands), you don't have to consider this problem at all. Many problems can be solved by waiting for a large amount of data to be solved by a certain program, because overdesign is really wasteful.

the query must have a sort field, otherwise the order of the results is unpredictable. If you have the sort field, you will also use the appropriate index.
see this answer

.

first of all, you can consider whether there is something wrong with your watch. Only record the number of likes. How can you make sure that each user only likes once?
well, let's say you have another praise table and here Synchronize is updated, so is it suitable for indexing?
that depends on how often you query this? If you query frequently, you still have to build an index, even if frequent updates have a certain impact on index maintenance (in fact, change_buffer is not so efficient)
the last SQL statement of yours uses a clustered index, that is, the index of the primary key

SELECT id FROM t_article WHERE id > 10000 ORDER BY like_count DESC LIMIT 0Pol 4;
only walks the index on id. Even if there is a joint index of id and like_count, it ignores the index of like_count column

.

Previous: How to introduce less? into jQuery Project

Next: How to check the URL of webview that app jumped to?

MySQL Query : SELECT * FROM `codeshelper`.`v9_news` WHERE status=99 AND catid='6' ORDER BY rand() LIMIT 5
MySQL Error : Disk full (/tmp/#sql-temptable-64f5-1b375c9-2b3ef.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
MySQL Errno : 1021
Message : Disk full (/tmp/#sql-temptable-64f5-1b375c9-2b3ef.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
Need Help?