В моей базе данных есть таблица wp_postmeta
, пример:
| meta_key | meta_value | post_id |
---------------------------------
points | 12 | 23
points | 2 | 18
lorem | ipsum | 92
points | 8 | 15
хочу заказать points
от meta_value
и получить номер строки определенного post_id
. По сути, это система рангов, с высшим числом наверху.
Например, заказ points
от meta_value
:
| meta_key | meta_value | post_id |
---------------------------------
points | 12 | 23
points | 8 | 15
points | 2 | 18
post_id
со значением 15
будет 2 место.
Какой SQL-запрос я могу выполнить для этого с участием в виду оптимизация?
Что я пробовал до сих пор
Я добился этого с помощью запроса:
$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;
Работает отлично. тем не мение, этот запрос выполняется очень медленно.
Как я могу сделать этот запрос быстрее?
РЕДАКТИРОВАТЬ: Вот список индексов в этой таблице:
2 ответа
Mysql поддерживает функцию ROW_NUMBER ().
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;
Но я не уверен, что он работает лучше, чем ваше решение.
Я бы создал индекс покрытия на meta_key
, meta_value
, post_id
и посмотрите, поможет ли это.
Обратите внимание, что в настоящее время у вас есть только префиксный ключ в столбце meta_key. Таким образом, вам, вероятно, придется создать префиксы meta_key и meta_value, и вам, возможно, придется ограничить их общей длиной 186 (при условии, что вам нужно 20 для post_id). Это, безусловно, работает для примера, где вы могли бы обойтись с
CREATE INDEX meta_key_value_post_id_idx ON wp_postmeta ( meta_key(7), meta_value(21), post_id )
Вам могут понадобиться или не потребоваться более длинные значения для meta_value
и / или meta_key
по другим запросам. Но если вы когда-либо запрашиваете только «точки», и у него всегда есть числовые значения из двадцати цифр или меньше, это должно сработать. Потому что этого достаточно для однозначного сопоставления точек (7 байтов больше, чем шесть однобайтовых символов в точках, поэтому они не будут соответствовать таким вещам, как точкиa) и числовым значениям (менее двадцати цифр ASCII).
Эта почта может помочь вам определить максимальную длину значения в столбце. Например
SELECT MAX(LENGTH(meta_value)) FROM wp_postmeta WHERE meta_key = 'points';
Примечание: предполагается, что вы используете версию @ choroba. Я не уверен, что индекс поможет с GROUP_CONCAT
.
Спасибо. Я провел тест скорости на этом против моего оригинального подхода, и, к сожалению, нет никакой разницы. Есть ли подход, при котором запрос будет работать лучше?
— Хенрик Петтерсон
У вас есть какие-либо индексы, определенные в таблице?
— болезнь
Да, это текущая настройка индекса этой таблицы: i.imgur.com/QReQbZj.png
— Хенрик Петтерсон