Question 165594 in Wordpress Question posted in
The official Wordpress documentation can be found here.

Can WordPress order a table by column & get row number?

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

So, post_id with value 15 will be rank 2.

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

What I’ve tried so far

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

Tags:

3

Answers


  1. 0 
    SELECT * FROM wp_postmeta WHERE meta_key = 'points' ORDER BY meta_value+0 DESC

    For the rest of this problem, a simple bit of php will suffice:

    <?php

/*
DROP TABLE IF EXISTS wp_postmeta;

CREATE TABLE wp_postmeta
(meta_key VARCHAR(12) NOT NULL
,meta_value VARCHAR(12) NOT NULL
,post_id INT NOT NULL PRIMARY KEY
,UNIQUE KEY(meta_key,meta_value)
);

INSERT INTO wp_postmeta VALUES
('points','12',23),
('points','2',18),
('lorem','ipsum',92),
('points','8',15);
*/

  require('path/to/pdo/connection/stateme.nts');
  $query = "SELECT post_id FROM wp_postmeta WHERE meta_key = 'points' ORDER BY meta_value+0 DESC";
  $data = $pdo->query($query)->fetchAll(PDO::FETCH_COLUMN, 0);


echo array_search('15',$data)+1;

?>

    Outputs: 2

    Login or Signup to reply.
  2. 0

    Use ROW_NUMBER() (or RANK() if there are ties) window function:

    SELECT *, ROW_NUMBER() OVER (ORDER BY meta_value + 0 DESC) `rank` 
FROM wp_postmeta 
WHERE meta_key = 'points' 
ORDER BY `rank`

    If you want the rank of a specific post_id:

    SELECT *
FROM (
  SELECT *, ROW_NUMBER() OVER (ORDER BY meta_value + 0 DESC) `rank` 
  FROM wp_postmeta 
  WHERE meta_key = 'points' 
) t
WHERE post_id = '15';

    Or:

    SELECT *, ROW_NUMBER() OVER (ORDER BY meta_value + 0 DESC) `rank` 
FROM wp_postmeta 
WHERE meta_key = 'points' 
ORDER BY post_id = '15' DESC LIMIT 1;
    Login or Signup to reply.
  3. 0

    If meta_value where stored as a number, the following might be the fastest approach:

    select count(*) + 1
from wp_postmeta pm
where pm.meta_key = 'points' and
      pm.meta_value > (select pm2.meta_value
                       from wp_postmeta pm2
                       where pm2.meta_key = 'points' and
                             pm2.post_id = 15
                      );

    This could take advantage of indexes on wp_postmeta(meta_key, post_id, meta_value) and wp_postmeta(meta_key, meta_value).

    However, meta_value is probably a string so the > comparison does not work. One option is a generated column, converting the value to a number. I’m not sure if modifying the data model is something you want to do.

    The goal is to avoid sorting by a non-index key. Another method does something similar, but like this:

    select count(*) + 1
from wp_postmeta pm cross join
     (select pm2.meta_value
      from wp_postmeta pm2
      where pm2.meta_key = 'points' and
            pm2.post_id = 15
     ) pm2
where pm.meta_key = 'points' and
      (pm.meta_value + 0) > (pm2.meta_value + 0);

    with an index on wp_postmeta(meta_key, post_id, meta_value). This should be an index lookup for pm2 and then an index scan for the points records for the outer query.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search