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:

    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:

enter image description here

  • \$\begingroup\$ Doesn't mysql now have ROW_NUMBER()? \$\endgroup\$
    – choroba
    Commented Jul 14, 2021 at 9:03
  • 1
    \$\begingroup\$ @choroba Can you please post an answer using ROW_NUMBER() with my code as reference? \$\endgroup\$ Commented Jul 14, 2021 at 9:17
  • \$\begingroup\$ I don't believe that there is enough context to actually answer this question. What are the inputs? What indexes do you have on the table? Please read the SQL tag wiki and provide the requested information. Note also that for many applications (e.g. a top ten list), it would be better to select the rank as part of the listing query. \$\endgroup\$
    – mdfst13
    Commented Jul 14, 2021 at 10:11
  • \$\begingroup\$ @mdfst13 List of indexes in this table: i.imgur.com/QReQbZj.png \$\endgroup\$ Commented Jul 14, 2021 at 10:57

4 Answers 4


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.


Mysql supports the ROW_NUMBER() function.

    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.

  • \$\begingroup\$ Thanks. I ran a speed test on this vs my original approach, and there is barely any difference unfortunately. Is there an approach where the query will perform better? \$\endgroup\$ Commented Jul 14, 2021 at 10:42
  • \$\begingroup\$ Do you have any indices defined on the table? \$\endgroup\$
    – choroba
    Commented Jul 14, 2021 at 10:44
  • \$\begingroup\$ Yes, this is the current index setup of this table: i.imgur.com/QReQbZj.png \$\endgroup\$ Commented Jul 14, 2021 at 10:49

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

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

this will return :

| post_id | rank |
|      15 |    2 |

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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.