Какой составной индекс сделает этот простой запрос MySQL быстрее?

Какой составной индекс сделает этот простой запрос MySQL быстрее, и как мне создать этот составной индекс?

SELECT * 
FROM  `Table1` 
WHERE  `col1` =  '145307'
AND  `col2` =  '0'
AND col3 NOT 
IN ( 130209, 130839 ) 
ORDER BY col4 DESC 
LIMIT 0 , 5

Уже есть отдельный индекс для каждого столбца выше (col1 в col4).

РЕДАКТИРОВАТЬ:

Результаты SHOW CREATE TABLE:

CREATE TABLE `Table1` (  
 `primaryCol` int(11) NOT NULL AUTO_INCREMENT,
 `col3` int(11) DEFAULT '0',
 `col5` varchar(20) COLLATE utf8_bin DEFAULT NULL,
 `col1` int(11) DEFAULT '0',
 `col6` varchar(80) COLLATE utf8_bin DEFAULT NULL,
 `col7` text CHARACTER SET utf8,
 `col4` int(11) DEFAULT '0',
 `col8` char(1) COLLATE utf8_bin DEFAULT 'N',
 `col9` char(1) COLLATE utf8_bin DEFAULT 'N',
 `col2` tinyint(1) NOT NULL,
 `col10` tinyint(1) NOT NULL,
 `col11` smallint(6) NOT NULL,
 PRIMARY KEY (`primaryCol`),
 KEY `col5` (`col5`),
 KEY `col1` (`col1`),
 KEY `col3` (`col3`),
 KEY `col4` (`col4`),
 KEY `col8` (`col8`),
 KEY `col9` (`col9`),
 KEY `CompIndex1` (`col1`,`col8`,`col4`),
 KEY `col2` (`col2`),
 KEY `col10` (`col10`),
 KEY `col11` (`col11`),
 FULLTEXT KEY `col7` (`col7`)
) ENGINE=MyISAM AUTO_INCREMENT=4575350 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

Результаты EXPLAIN EXTENDED:

id  select_type table   type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  Table1  ref col1,col3,CompIndex1,col2   CompIndex1  5   const   226 100 Using where; Using filesort
Источник

3 ответа

Решение

Я бы предложил индекс на (col1, col2, col3).

mysql> CREATE INDEX NewIndex ON Table1 (col1,col2,col3);

mysql> EXPLAIN SELECT *  FROM  `Table1`  WHERE  `col1` =  '145307' 
AND  `col2` =  '0' AND col3 NOT  IN ( 130209, 130839 )  
ORDER BY col4 DESC  LIMIT 0 , 5\G

           id: 1
  select_type: SIMPLE
        table: Table1
         type: ref
possible_keys: col1,col3,CompIndex1,col2,NewIndex
          key: NewIndex
      key_len: 6
          ref: const,const
         rows: 1
        Extra: Using where; Using filesort

Ваше условие на col3 - это не сравнение на равенство, это сравнение диапазона, и это должен быть последний столбец в индексе.

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

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

Смотрите также мою презентацию " Как правильно составлять индексы".

Источник

Текущий лучший ответ - самый ужасный. Каждый раз, когда вы видите "сортировку файлов" в EXPLAIN, она будет ужасно медленной, потому что MySQL должен создавать временные файлы и не использовать индекс для сортировки!

Индексы похожи на телефонные книги. В вашем случае правильный индекс будет:

(col4, col1, col2, col3, primaryCol)

Порядок имеет значение! Крайней левой частью вашего составного индекса всегда должны быть столбцы, по которым вы хотите упорядочить. Затем добавьте другие столбцы, которые вы используете в предложении WHERE. Наконец, для MyISAM вам также необходимо добавить первичный ключ (InnoDB делает это автоматически).

Кроме того, вам нужно изменить свой запрос, чтобы упорядочить / отфильтровать и получить полную информацию:

SELECT * FROM `Table1` JOIN (
  SELECT primaryKey FROM  `Table1` 
  WHERE  `col1` =  '145307'
  AND  `col2` =  '0'
  AND col3 NOT 
  IN ( 130209, 130839 ) 
  ORDER BY col4 DESC 
  LIMIT 0 , 5
) foo ON Table1.primaryKey=foo.primaryKey

Проверяя это с EXPLAIN, вы увидите правильное использование индекса для упорядочения и фильтрации. Затем идентификаторы результатов снова объединяются с таблицей (в другой операции быстрого индексирования), чтобы получить полную информацию.

Источник

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

Источник
Другие вопросы по тегам