Ответ 1

Поскольку проблема HAVING кажется проблемой, можете ли вы вместо этого создать поле флага в таблице posts и использовать это вместо этого? Если я правильно понял запрос, вы пытаетесь найти сообщения только с одной ссылкой meta_relationship. Если вы создали поле в своей таблице сообщений, которое было либо числом мета-ссылок для этой записи, либо логическим флагом для того, было ли только одно и проиндексировано, конечно, это, вероятно, было бы намного быстрее. Это было бы связано с обновлением поля, если сообщение было отредактировано.

Итак, рассмотрим следующее:

Добавить новое поле в таблицу сообщений, называемую num_meta_rel. Это может быть неподписанный tinyint, если у вас никогда не будет более 255 тегов для любого сообщения.

Обновите поле следующим образом:

UPDATE posts
SET num_meta_rel=(SELECT COUNT(object_id) from meta_relationships WHERE object_id=posts.post_id);

Для выполнения этого запроса потребуется некоторое время, но как только вы закончите, вы получите все подсчеты. Обратите внимание, что это можно сделать лучше с помощью соединения, но SQLite (Ideone) разрешает только подзапросы.

Теперь вы переписываете свой запрос следующим образом:

SELECT
posts.post_id,posts.post_name,
GROUP_CONCAT(IF(meta_data.type = 'category', meta.meta_name,null)) AS category,
GROUP_CONCAT(IF(meta_data.type = 'tag', meta.meta_name,null)) AS tag
FROM posts
RIGHT JOIN meta_relationships ON (posts.post_id = meta_relationships.object_id)
LEFT JOIN meta_data ON meta_relationships.meta_data_id = meta_data.meta_data_id
LEFT JOIN meta ON meta_data.meta_id = meta.meta_id
WHERE meta.meta_name = computers AND posts.num_meta_rel=1
GROUP BY meta_relationships.object_id

Если я сделал это правильно, исполняемый код здесь: http://ideone.com/ZZiKgx

Обратите внимание, что для этого решения требуется обновить num_meta_rel (выберите лучшее имя, это ужасно...), если сообщение имеет новый тег, связанный с ним. Но это должно быть намного быстрее, чем сканирование всей таблицы снова и снова.

Ответ 2

При сочетании оптимизированных запросов И оптимизации ваших таблиц вы получите быстрые запросы. Однако вы не можете иметь быстрые запросы без оптимизированной таблицы.

Я не могу этого достаточно подчеркнуть: если ваши таблицы правильно структурированы с правильным количеством индексов, вы не должны испытывать никаких полных табличных чтений в запросе типа GROUP BY... HAVING, если вы не сделаете это по дизайну.

На основе вашего примера я создал этот SQLFiddle.

Сравните это с SQLFiddle # 2, в котором я добавил индексы и добавил индекс UNIQUE против meta.meta_naame.

Из моего тестирования Fiddle # 2 работает быстрее.

Оптимизация запроса

Этот запрос приводил меня в бешенство, даже после того, как я сделал аргумент, что индексы будут лучшим способом его оптимизировать. Несмотря на то, что я все еще считаю, что таблица - это ваша самая большая возможность повысить производительность, казалось, что в MySQL должен быть лучший способ запустить этот запрос. У меня было откровение после спящего режима по этой проблеме, и я использовал следующий запрос (см. в SQLFiddle # 3):

SELECT posts.post_id,posts.post_name,posts.post_title,posts.post_description,posts.date,meta.meta_name
   FROM posts
   LEFT JOIN meta_relationships ON meta_relationships.object_id = posts.post_id
   LEFT JOIN meta_data ON meta_relationships.meta_data_id = meta_data.meta_data_id
   LEFT JOIN meta ON meta_data.meta_id = meta.meta_id
   WHERE meta.meta_name = 'animals'
   GROUP BY meta_relationships.object_id
   HAVING sum(meta_relationships.object_id) = min(meta_relationships.object_id);

HAVING sum() = min() на a GROUP BY должен проверить, есть ли более одной записи каждого типа. Очевидно, каждый раз, когда появляется запись, она добавит больше к сумме. (Edit: В последующих тестах похоже, что это имеет такое же влияние, как count(meta_relationships.object_id) = 1. О, ну, я думаю, вы можете удалить подзапрос и получить тот же результат).

Я хочу быть уверенным, что вы не заметите много, если бы какая-либо оптимизация по запросу, который я вам предоставил, если раздел WHERE meta.meta_name = 'animals' не запрашивает индекс (желательно уникальный индекс, потому что я сомневаюсь, что вам понадобится больше, чем один из них и предотвратит случайное дублирование данных).

Итак, вместо таблицы, которая выглядит так:

CREATE TABLE meta_data (
  meta_data_id BIGINT,
  meta_id BIGINT,
  type VARCHAR(50),
  description VARCHAR(200),
  parent BIGINT,
  count BIGINT);

Вы должны убедиться, что добавили первичные ключи и индексы, например:

CREATE TABLE meta_data (
  meta_data_id BIGINT,
  meta_id BIGINT,
  type VARCHAR(50),
  description VARCHAR(200),
  parent BIGINT,
  count BIGINT,
  PRIMARY KEY (meta_data_id,meta_id),
  INDEX ix_meta_id (meta_id)
);

Не переусердствуйте, но каждая таблица должна иметь первичный ключ, и в любое время, когда вы агрегируете или запрашиваете конкретное значение, должны быть индексы.

Когда индексы не используются, MySQL будет проходить через каждую строку таблицы, пока не найдет то, что вы хотите. В таком ограниченном примере, как ваш, это не займет слишком много времени (хотя оно все еще заметно медленнее), но когда вы добавляете тысячи и более записей, это станет чрезвычайно болезненным.

В будущем при просмотре ваших запросов попытайтесь определить, где происходят полные сканирование таблицы, и посмотрите, есть ли индекс в этом столбце. Хорошим местом для начала является то, где вы агрегируете или используете синтаксис WHERE.

Заметка в столбце count

Я не нашел класть столбцы count в таблицу, чтобы быть полезной. Это может привести к довольно серьезным проблемам с целостностью. Если таблица правильно оптимизирована, должно быть очень просто использовать count() и получить текущий счетчик. Если вы хотите иметь его в таблице, вы можете использовать VIEW, хотя это не самый эффективный способ сделать pull.

Проблема с помещением столбцов count в таблицу состоит в том, что вам нужно обновить этот счет, используя либо логику TRIGGER, либо, что хуже, приложения. Поскольку ваша программа масштабирует эту логику, можно либо потеряться, либо похоронить. Добавление этого столбца является отклонением от нормализации, и когда произойдет что-то подобное, должна существовать серьезная причина ОЧЕНЬ.

Некоторые дебаты о том, есть ли когда-либо веская причина для этого, но я думаю, что было бы разумно держаться подальше от этих дебатов, потому что есть большие аргументы с обеих сторон. Вместо этого я выберу гораздо меньшую битву и скажу, что вижу, что это вызывает у вас больше головных болей, чем пользы в этом случае, поэтому, вероятно, стоит проверить A/B.

Ответ 3

Посмотрите, дает ли это правильный ответ, возможно, быстрее:

SELECT  p.post_id, p.post_name,
        GROUP_CONCAT(IF(md.type = 'category', meta.meta_name, null)) AS category,
        GROUP_CONCAT(IF(md.type = 'tag', meta.meta_name, null)) AS tag
    FROM  
      ( SELECT  object_id
            FROM  meta_relation
            GROUP BY  object_id
            HAVING  count(*) = 1 
      ) AS x
    JOIN  meta_relation AS mr ON mr.object_id = x.object_id
    JOIN  posts AS p ON p.post_id = mr.object_id
    JOIN  meta_data AS md ON mr.meta_data_id = md.meta_data_id
    JOIN  meta ON md.meta_id = meta.meta_id
    WHERE  meta.meta_name = ?
    GROUP BY  mr.object_id 

Ответ 4

К сожалению, у меня нет возможности проверить производительность,

Но попробуйте мой запрос, используя ваши реальные данные:

http://sqlfiddle.com/#!9/81b29/13

SELECT
posts.post_id,posts.post_name,
GROUP_CONCAT(IF(meta_data.type = 'category', meta.meta_name,null)) AS category,
GROUP_CONCAT(IF(meta_data.type = 'tag', meta.meta_name,null)) AS tag
FROM posts
INNER JOIN (
  SELECT meta_relationships.object_id
   FROM meta_relationships 
   GROUP BY meta_relationships.object_id 
   HAVING count(*) < 3
  ) mr ON mr.object_id = posts.post_id
LEFT JOIN meta_relationships ON mr.object_id = meta_relationships.object_id
LEFT JOIN meta_data ON meta_relationships.meta_data_id = meta_data.meta_data_id
INNER JOIN (
  SELECT * 
  FROM meta
  WHERE  meta.meta_name = 'health'
  ) meta ON meta_data.meta_id = meta.meta_id
GROUP BY posts.post_id

Ответ 5

Использование

sum(1)

вместо

count(*)