Adding a rank value to a MySQL table

Question

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

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`) ) 

I want to create the rank of the 7th column, which will determine the rank of the rows ordered by the combined_frequency column. I do this to reduce overhead when the database is live.

Can I do this with MySQL statements or do I need to write a bunch of SELECT / INSERT statements in PHP (for example)? I do most of the work in PHP, but it takes up to 24 hours to complete the operations on the table.

I looked at the RANK function, but since my MySQL ability was only honest, I ran into problems.

Answer 1

Normally, I would not consider "do not do this" to be the correct answer to such a question, but you will not help in the situation by adding a new column. You already have a numeric column to order your data. Adding a second numeric column for this purpose is not only excessive destruction, but also poor design.

If it takes "up to 24 hours" to perform any operation on a table containing only 80,000 rows (which is not much), you need to add indexes to the columns used for search / sort / join.

Answer 2

This is how you add rank to the table:

  SET @rownum = 0; SELECT *, (@rownum := @rownum + 1) AS rank FROM word_list_master ORDER BY combined_frequency DESC; 

Update: If you want to write queries against this rank field, you must use the previous table as a derived table as follows:

 SET @rownum = 0; SELECT * FROM ( SELECT *, (@rownum := @rownum + 1) AS rank FROM word_list_master ORDER BY combined_frequency DESC; ) t WHERE rank = 5 

And if you want to fine-tune your query as follows:

 SET @rownum = 0; SELECT * FROM ( SELECT *, (@rownum := @rownum + 1) AS rank FROM word_list_master ORDER BY combined_frequency DESC; ) t WHERE rank BETWEEN ((@PageNum - 1) * @PageSize + 1) AND (@PageNum * @PageSize) 

Update:

Please note that for this there is already a built way to do this instead of this terrible query, which is [LIMIT {[offset,] row_count }] , which is the standard mysql path for this. Do not use the previous method if you just want to get a limited set of results from your query.