在我的MySQL表中添加等级值 [英] Adding a rank value to my MySQL table

问题描述

我有一个不会改变的固定桌子.我有80,000多行.

I have a fixed table that will not change. I have over 80,000 rows.

CREATE TABLE `word_list_master` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `word` varchar(64) NOT NULL,
  `created` int(11) NOT NULL,
  `frequency` int(11) NOT NULL,
  `number_of_files` int(11) NOT NULL,
  `combined_frequency` bigint(24) NOT NULL,
  PRIMARY KEY (`id`)
)

我想创建第7列等级"，该列将确定Combined_frequency列排序的行的等级.我这样做是为了减少数据库处于活动状态时的开销.

I want to create a 7th column 'rank' that will determine the rank of the rows ordered by the combined_frequency column. I am doing this to reduce overhead when the database is live.

我可以通过MySQL语句执行此操作，还是必须在PHP中编写一堆SELECT/INSERT语句(例如)?到目前为止，我已经在PHP中完成了大部分工作，但是要在表上执行操作最多需要24小时.

Can I do this via MySQL statement(s) or do I have to write a bunch of SELECT/INSERT statements in PHP (for example)? I have been doing most of the work so far in PHP but it is taking up to 24 hours to perform operations on the table.

我当时在查看RANK函数，但是由于我的MySQL能力只是中等水平，因此遇到了问题.

I was looking at the RANK function but as my MySQL ability is only fair in capacity, I ran into problems.

推荐答案

通常，我不会将不这样做"视为此类问题的有效答案，但您不会帮助您解决问题通过添加一个新列.您已经拥有一个数字列，可用来对数据进行排序.为此添加第二个数字列不仅过分，而且设计不好.

Normally I wouldn't consider "don't do this" to be a valid answer to such a question, but you're not going to help your situation by adding a new column. You already have a numeric column by which to order your data. Adding a second numeric column for this purpose is not only over-kill, it is bad design.

如果对仅包含80,000行(不是很多)的表进行任何操作需要最多24小时"，那么您需要

If it's taking "up to 24 hours" to do any operation on a table containing only 80,000 rows (which is not very many) then you need to add indexes to the columns being used to search/sort/join.

这篇关于在我的MySQL表中添加等级值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！