Revisions to Order a table by column and get row number

Bumped by Community user

occurred Mar 22, 2022 at 0:04

Bumped by Community user

occurred Nov 22, 2021 at 0:01

Bumped by Community user

occurred Aug 13, 2021 at 14:07

Tweeted twitter.com/StackCodeReview/status/1415370540068573184

occurred Jul 14, 2021 at 18:00

added 142 characters in body

Source Link

edited Jul 14, 2021 at 10:58

Henrik Petterson

241
1
6

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:

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?

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:

added 9 characters in body

Source Link

edited Jul 14, 2021 at 9:07

Henrik Petterson

241
1
6

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 table by points by meta_value and get the row number based onof a specific meta_valuepost_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?

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 table by points and get the row number based on meta_value. 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?

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?

added 2 characters in body

Source Link

edited Jul 14, 2021 at 8:46

Henrik Petterson

241
1
6

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 this table by points and get the row number based on post_idmeta_value. Basically a rank system, with highest number at the top.

For example, ordered byordering 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?

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 this table by points and get the row number based on post_id. Basically a rank system, with highest number at the top.

For example, ordered by points:

| 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?

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 table by points and get the row number based on meta_value. 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?

Source Link

asked Jul 14, 2021 at 8:37

Henrik Petterson

241
1
6

Loading

Stack Exchange Network

Return to Question

What I've tried so far

What I've tried so far

What I've tried so far

What I've tried so far

What I've tried so far

What I've tried so far

What I've tried so far

What I've tried so far

What I've tried so far