Ответ 1

Слишком много записей для подсчета.

Если вам требуется более быстрое решение, вам нужно будет хранить агрегированные данные.

MySQL не поддерживает материализованные представления (или индексированные представления в терминах SQL Server), поэтому вам нужно будет создавать и поддерживать их самостоятельно.

Создать таблицу:

CREATE TABLE
        page_active_category
        (
        active INT NOT NULL,
        catid INT NOT NULL,
        cnt BIGINT NOT NULL,
        PRIMARY KEY
                (active, catid)
        ) ENGINE=InnoDB;

затем заполнить его:

INSERT
INTO    page_active_category
SELECT  active, catid, COUNT(*)
FROM    content_en
JOIN    content_page_categories
ON      itemid = id
GROUP BY
        active, catid

Теперь, каждый раз, когда вы вставляете, удаляете или обновляете запись в content_en или content_page_categories, вы должны обновить соответствующую запись в page_active_category.

Это можно сделать с двумя простыми триггерами как на content_en, так и на content_page_categories.

Таким образом, ваш исходный запрос может быть переписан как простой:

SELECT  cnt
FROM    page_active_category
WHERE   active = 1
        AND catid = 1

который является единственным поиском первичного ключа и, следовательно, мгновенным.

Ответ 2

Я загрузил ваши данные и попробовал несколько экспериментов. Я запускаю MySQL 5.6.12 на виртуальной машине CentOS на Macbook Pro. Времена, которые я наблюдал, могут использоваться для сравнения, но ваша система может иметь разную производительность.

Базовый футляр

Сначала я пробовал без предложений USE INDEX, потому что я избегаю переопределения оптимизаторов, где это возможно. В большинстве случаев простой запрос, например, должен использовать правильный индекс, если он доступен. Жесткое кодирование выбора индекса в запросе затрудняет использование лучшего индекса позже.

Я также использую имена корреляции (псевдонимы таблиц), чтобы сделать запрос более понятным.

mysql> EXPLAIN SELECT COUNT(*) FROM content_en AS e  
INNER JOIN content_page_categories AS c ON c.itemid = e.id 
WHERE c.catid = 1 AND e.active = 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: c
         type: ref
possible_keys: combo,combo2
          key: combo
      key_len: 4
          ref: const
         rows: 71198
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: e
         type: eq_ref
possible_keys: PRIMARY,combo2,combo
          key: PRIMARY
      key_len: 4
          ref: test.c.itemid
         rows: 1
        Extra: Using where

• Это выполняется за 0,36 секунды.

Индекс покрытия

Я бы также хотел получить "Использование индекса" во второй таблице, поэтому мне нужен индекс (active, id) в этом порядке. Я должен был использовать INDEX в этом случае, чтобы убедить оптимизатора не использовать первичный ключ.

mysql> ALTER TABLE content_en ADD KEY combo2 (active, id);

mysql> explain SELECT COUNT(*) FROM content_en AS e USE INDEX (combo2) 
INNER JOIN content_page_categories AS c ON c.itemid = e.id 
WHERE c.catid = 1 AND e.active = 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: c
         type: ref
possible_keys: combo,combo2
          key: combo
      key_len: 4
          ref: const
         rows: 71198
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: e
         type: ref
possible_keys: combo2
          key: combo2
      key_len: 6
          ref: const,test.c.itemid
         rows: 1
        Extra: Using where; Using index

rows, сообщенный EXPLAIN, является важным индикатором того, сколько работы он предпримет для выполнения запроса. Обратите внимание, что rows в приведенном выше EXPLAIN только 71k, намного меньше, чем 125k строк, которые вы получили при первом просмотре таблицы content_en.

• Выполняется через 0,44 секунды. Это неожиданно, потому что обычно запрос с использованием индекса покрытия является улучшением.

Преобразование таблиц в InnoDB

Я попробовал такое же решение индекса покрытия, как указано выше, но с InnoDB в качестве механизма хранения.

mysql> ALTER TABLE content_en ENGINE=InnoDB;
mysql> ALTER TABLE content_page_categories ENGINE=InnoDB;

У этого был тот же отчет EXPLAIN. Для нагрева буферного пула потребовалось 1 или 2 итерации, но затем производительность запроса утроилась.

• Выполняется через 0,16 секунды.

• Я также попытался удалить USE INDEX, а время немного увеличилось, до 0,17 секунды.

Решение @Matthew с STRAIGHT_JOIN

mysql> SELECT straight_join count(*) 
 FROM content_en 
 INNER JOIN content_page_categories use index (combo) 
  ON (id = itemid) 
 WHERE catid = 1 AND active = 1;

• Это выполняется за 0.20 - 0.22 секунды.

Решение @bobwienholt, денормализация

Я попробовал решение, предложенное @bobwienholt, используя денормализацию, чтобы скопировать атрибут active в таблицу content_page_categories.

mysql> ALTER TABLE content_page_categories ADD COLUMN active TINYINT(1);
mysql> UPDATE content_en JOIN content_page_categories ON id = itemid 
    SET content_page_categories.active = content_en.active;
mysql> ALTER TABLE content_page_categories ADD KEY combo3 (catid,active);
mysql> SELECT COUNT(*) FROM content_page_categories WHERE catid = 1 and active = 1;

Выполняется в 0.037 - 0.044 секунд. Так что это лучше, если вы можете поддерживать избыточный столбец active в синхронизации со значением в таблице content_en.

@Quassnoi решение, сводная таблица

Я попробовал решение, предложенное @Quassnoi, для поддержания таблицы с предварительно вычисленными значениями для каждого кота и активного. В таблице должно быть очень мало строк, и поиск нужных вам счетчиков - это поиск первичных ключей и не требует JOINs.

mysql> CREATE TABLE page_active_category (
 active INT NOT NULL, 
 catid INT NOT NULL, 
 cnt BIGINT NOT NULL,
 PRIMARY KEY (active, catid) 
) ENGINE=InnoDB;

mysql> INSERT INTO page_active_category
 SELECT  e.active, c.catid, COUNT(*)
 FROM    content_en AS e
 JOIN    content_page_categories AS c ON c.itemid = e.id
 GROUP BY e.active, c.catid

mysql> SELECT cnt FROM page_active_category WHERE active = 1 AND catid = 1

Выполняется в 0.0007 - 0.0017 секунд. Таким образом, это лучшее решение на порядок, если вы можете поддерживать таблицу с подсчетом агрегатов.

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

Ответ 3

Проблема заключается в "активном" столбце в content_en. Очевидно, что если вам просто нужно знать, сколько записей контента было связано с определенной категорией (активной или нет), все, что вам нужно сделать, это:

SELECT count(1)
FROM content_page_categories
WHERE catid = 1;

Чтобы присоединиться к каждой записи content_en, просто прочитать "активный" флаг - это действительно то, что замедляет этот запрос.

Я рекомендую добавить "active" в content_page_categories и сделать его копией связанного значения в content_en... вы можете обновить этот столбец с помощью триггеров или кода. Затем вы можете изменить индекс комбо:

KEY combo (catid,active,itemid)

и перепишите свой запрос на:

SELECT count(1)
FROM content_page_categories USE INDEX (combo)
WHERE catid = 1 AND active = 1;

Кроме того, вам может быть намного лучше использовать таблицы InnoDB вместо MyISAM. Просто не забудьте настроить настройки InnoDB: http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/

Ответ 4

Для меня с вашими данными в качестве настройки, я получал запрос на соединение в ~ 50 раз больше, чем просто выбор из content_page_categories.

Мне удалось добиться производительности примерно в 10 раз медленнее, чем просто выбрать из таблицы категорий, выполнив следующие данные:

Я использовал straight_join

    SELECT straight_join count(*) 
    FROM content_en 
    INNER JOIN content_page_categories use index (combo) 
     ON (id = itemid) 
    WHERE catid = 1 AND active = 1 ;

и следующая структура таблицы (слегка измененная):

 CREATE TABLE `content_en` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `title` varchar(100) DEFAULT NULL,
 `uid` int(11) DEFAULT NULL,
 `date_added` int(11) DEFAULT NULL,
 `date_modified` int(11) DEFAULT NULL,
 `active` tinyint(1) DEFAULT NULL,
 `comment_count` int(6) DEFAULT NULL,
 `orderby` tinyint(4) DEFAULT NULL,
 `settings` text,
 `permalink` varchar(255) DEFAULT NULL,
 `code` varchar(3) DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `id` (`id`),
 KEY `test_con_1` (`active`) USING HASH,
 KEY `combo` (`id`,`active`) USING HASH
 ENGINE=MyISAM AUTO_INCREMENT=127126 DEFAULT CHARSET=utf8

и

CREATE TABLE `content_page_categories` (
`catid` int(11) unsigned NOT NULL,
`itemid` int(10) unsigned NOT NULL,
`main` tinyint(1) DEFAULT NULL,
KEY `itemid` (`itemid`),
KEY `catid` (`catid`),
KEY `test_cat_1` (`catid`) USING HASH,
KEY `test_cat_2` (`itemid`) USING HASH,
KEY `combo` (`itemid`,`catid`) USING HASH
ENGINE=MyISAM DEFAULT CHARSET=utf8

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

EDIT:

Я должен также указать на этот достойный пост о том, почему и когда нужно быть осторожным с Straight_Join: Когда использовать STRAIGHT_JOIN с MySQL

Если вы используете его, используйте его ответственно!

Ответ 5

чтобы ускорить подсчет соединений mysql, используйте подзапросы.

Например, получение городов с помощью placeCount

стол города

название титула......

место стол

id city_id title.....

SELECT city.title,subq.count as placeCount
FROM city
       left join (
         select city_id,count(*) as count from place
         group by city_id
  ) subq
on city.id=subq.city_id