Order a table by column and get row number

Question

In my database, I have table wp_postmeta, example:

| meta_key | meta_value |  post_id   |
---------------------------------
   points  |     12     |  23
   points  |     2      |  18
   lorem   |     ipsum  |  92
   points  |     8      |  15

I want to order points by meta_value and get the row number of a specific post_id. Basically a rank system, with highest number at the top.

For example, ordering points by meta_value:

| meta_key | meta_value |  post_id   |
---------------------------------
   points  |     12     |  23
   points  |     8      |  15
   points  |     2      |  18

post_id with value 15 will be rank 2.

What SQL query can I run to achieve this with optimisation in mind?

What I've tried so far

I have achieved this via query:

$query="
    SELECT post_id,FIND_IN_SET(
                  post_id,(SELECT GROUP_CONCAT( post_id ORDER BY meta_value * 1 DESC)
                          FROM $wpdb->postmeta
                          WHERE meta_key ='points')
                       ) AS `rank`
    FROM $wpdb->postmeta
    WHERE meta_key ='points'
    AND post_id = '".$post_id."'
    ";

$result  = $wpdb->get_row($query);

$rank = $result->rank;

Works fine. However, this query is very slow.

How can I make this query faster?

EDIT: Here is a list of indexes in this table:

Answer 1

I would create a covering index on meta_key, meta_value, post_id and see if that helps.

Note that you currently only have a prefix key on the meta_key column. So you will probably have to make both meta_key and meta_value prefixes and you may have to limit them to a combined length of 186 (assuming you need 20 for the post_id). That certainly works for the example, where you could get by with

CREATE INDEX meta_key_value_post_id_idx ON wp_postmeta ( meta_key(7), meta_value(21), post_id )

You may or may not need longer values for meta_value and/or meta_key for other queries. But if you only ever query for 'points' and it always has numeric values with twenty digits or fewer, this should work. Because it's enough to match points uniquely (7 bytes is more than the six one-byte characters in points, so it won't match things like pointsa) and numeric values (less than twenty ASCII digits).

This post may help you determine what the maximum value length in the column is. E.g.

SELECT MAX(LENGTH(meta_value)) FROM wp_postmeta WHERE meta_key = 'points';

Note: this is assuming you use @choroba's version. I'm not sure that the index will help with GROUP_CONCAT.

Answer 2

Mysql supports the ROW_NUMBER() function.

SELECT rank
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY meta_value) AS rank,
        meta_key, meta_value, post_id
    FROM wp_postmeta
    WHERE meta_key = 'points'
) AS r
WHERE post_id = 18;

But I'm not sure it performs any better than your solution.

Answer 3

you could make use of variables and subqueries, something like this SQLFiddle:

SET @rank := 0;
SELECT post_id, rank
FROM (
  SELECT *, @rank := @rank + 1 rank
  FROM (
    SELECT 
      post_id, 
      meta_value * 1 meta_value
    FROM 
      wp_postmeta 
    WHERE
      meta_key ='points'
  ) a 
  ORDER BY meta_value desc
) e
WHERE 
  post_id = 15

this will return :

| post_id | rank |
|---------|------|
|      15 |    2 |

Answer 4

Sorry for bumping this old question, but I found it interesting and wanted to provide an alternative point of view.

To my understanding post_meta table is a bad place to store data which need to be queried or compared. I think this was due to meta_value column having longtext as its type - and the table itself getting bloated very easily. (Someone more knowledgeable of sql databases can comment, if this is the case or not.)

An alternative approach, which might be worth trying, is converting the meta values to posts. So you would have a custom post type points with the amount of points stored in the menu_order column (int(11)) and the relation to the actual post saved in post_parent (bigint(20) unsigned).

The query would then target the $wpdb->posts table where post_type='points' and post_parent=$post_id and order the results by menu_order column.

WP Codex: Post table description & indexes

Storing the data this way would of course require changes to how the points are stored. The custom post type could be registered as private (i.e. 'public' => false) so it doesn't show up in the admin UI.