Я хочу извлечь результаты из таблиц EAV (сущность-атрибут-значение) или, более конкретно, таблиц сущностей-метаданных (думаю, как wordpress wp_posts и wp_postmeta) как "красиво отформатированная реляционная таблица", чтобы выполнить некоторую сортировку и/или фильтрацию.
Я нашел несколько примеров того, как отформатировать результаты в запросе (в отличие от написания 2 запросов и объединения результатов в коде), но я хотел бы знать "самый эффективный" метод для этого, особенно для больших наборов результатов.
И когда я говорю "наиболее эффективный", я имею в виду что-то вроде следующих сценариев:
Получить все сущности с именем, например XYZ
Возвращает список объектов, отсортированных по дню рождения
например. поверните это:
** ENTITY ** ----------------------- ID | NAME | whatever ----------------------- 1 | bob | etc 2 | jane | etc 3 | tom | etc ** META ** ------------------------------------ ID | EntityID | KEY | VALUE ------------------------------------ 1 | 1 | first name | Bob 2 | 1 | last name | Bobson 3 | 1 | birthday | 1983-10-10 . | 2 | first name | Jane . | 2 | last name | Janesdotter . | 2 | birthday | 1983-08-10 . | 3 | first name | Tom . | 3 | last name | Tomson . | 3 | birthday | 1980-08-10
в это:
** RESULTS ** ----------------------------------------------- EID | NAME | first name | last name | birthday ----------------------------------------------- 1 | bob | Bob | Bobson | 1983-10-10 2 | jane | Jane | Janesdotter | 1983-08-10 3 | tom | Tom | Tomson | 1980-08-10
поэтому я могу сортировать или фильтровать любое из мета-полей.
Я нашел несколько предложений здесь, но я не могу найти никакого обсуждения того, что работает лучше.
Опции
SELECT e.*, GROUP_CONCAT( CONCAT_WS('||', m.KEY, m.VALUE) ORDER BY m.KEY SEPARATOR ';;' )
FROM `ENTITY` e JOIN `META` m ON e.ID = m.EntityID
SELECT e.*, m1.VALUE as 'first name', m2.VALUE as 'last name', m3.VALUE as 'birthday'
FROM `ENTITY` e
LEFT JOIN `META` m1
ON e.ID = m1.EntityID AND m1.meta_key = 'first name'
LEFT JOIN `META` m2
ON e.ID = m2.EntityID AND m2.meta_key = 'last name'
LEFT JOIN `META` m3
ON e.ID = m3.EntityID AND m3.meta_key = 'birthday'
SELECT e.*
, MAX( IF(m.KEY= 'first name', m.VALUE, NULL) ) as 'first name'
, MAX( IF(m.KEY= 'last name', m.VALUE, NULL) ) as 'last name'
, MAX( IF(m.KEY= 'birthday', m.VALUE, NULL) ) as 'birthday'
FROM `ENTITY` e
JOIN `META` m
ON e.ID = m.EntityID
SELECT e.* FROM `ENTITY` e WHERE e.ID = {whatever};
в PHP, создать объект-заполнитель из результата
SELECT m.* FROM `META` m WHERE m.EntityID = {whatever};
в PHP, просмотреть результаты и прикрепить к объекту объекта, например: $e->{$result->key} = $result->VALUEЧто лучше вообще, а для фильтрации/сортировки?
Похожие вопросы:
Все, что используется с помощью поворота или агрегатов, вероятно, будет быстрее, поскольку они не требуют, чтобы таблица была объединена. Подходы, основанные на объединении, потребуют от оптимизатора выполнить несколько операций подзапроса, а затем объединить результаты. Для небольшого набора данных это может не иметь особого значения, но это может значительно снизить производительность, если вы выполняете аналитический запрос в более крупном наборе данных,
Лучший способ узнать - это проверить, конечно. Ответ может отличаться в зависимости от размера набора данных, количества различных мета-ключей, их распределения (все ли сущности имеют значения для всех мета-ключей? Или только для некоторых из них?), Настройки вашей базы данных сервером и, возможно, многими другими факторами.
Если бы я предполагал, я бы сказал, что стоимость операций JOIN в варианте 2 будет меньше стоимости GROUP BY и агрегатных функций, необходимых в вариантах 1 и 3.
Итак, я ожидал бы найти вариант 2 быстрее, чем 1 и 3.
Чтобы измерить вариант 4, вам придется учитывать больше факторов, поскольку приложение может находиться на другом сервере, поэтому нагрузки двух серверов (db и application) и количества клиентов, которые будут запрашивать эти результаты, должны быть учитывается.
Sidenote: вам нужно GROUP BY e.ID в вариантах 1 и 3.