3
\$\begingroup\$

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:

enter image description here

\$\endgroup\$
4
  • \$\begingroup\$ Doesn't mysql now have ROW_NUMBER()? \$\endgroup\$
    – choroba
    Jul 14 at 9:03
  • 1
    \$\begingroup\$ @choroba Can you please post an answer using ROW_NUMBER() with my code as reference? \$\endgroup\$ Jul 14 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
    Jul 14 at 10:11
  • \$\begingroup\$ @mdfst13 List of indexes in this table: i.imgur.com/QReQbZj.png \$\endgroup\$ Jul 14 at 10:57
0
\$\begingroup\$

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.

\$\endgroup\$
3
  • \$\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\$ Jul 14 at 10:42
  • \$\begingroup\$ Do you have any indices defined on the table? \$\endgroup\$
    – choroba
    Jul 14 at 10:44
  • \$\begingroup\$ Yes, this is the current index setup of this table: i.imgur.com/QReQbZj.png \$\endgroup\$ Jul 14 at 10:49
0
\$\begingroup\$

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.

\$\endgroup\$
0
\$\begingroup\$

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 |
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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