У меня есть такой запрос
$query = "SELECT * FROM tbl_comments WHERE id=222 ORDER BY comment_time";
Мне нужно добавить индекс в поле comment_time
?
Кроме того, если я хочу получить данные между двумя датами, как мне построить индекс?
У меня есть такой запрос
$query = "SELECT * FROM tbl_comments WHERE id=222 ORDER BY comment_time";
Мне нужно добавить индекс в поле comment_time
?
Кроме того, если я хочу получить данные между двумя датами, как мне построить индекс?
Да, index поможет вам при использовании ORDER BY. Поскольку ИНДЕКС - это отсортированная структура данных, запрос будет выполняться быстрее.
Посмотрите на этот пример: таблица test2 с 3 строками. Я использовал LIMIT после order by, чтобы показать разницу в исполнении.
DROP TABLE IF EXISTS `test2`;
CREATE TABLE `test2` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`value` varchar(10) CHARACTER SET utf8 COLLATE utf8_swedish_ci NOT NULL,
PRIMARY KEY (`id`),
KEY `ix_value` (`value`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of test2
-- ----------------------------
INSERT INTO `test2` VALUES ('1', '10');
INSERT INTO `test2` VALUES ('2', '11');
INSERT INTO `test2` VALUES ('2', '9');
-- ----------------------------
-- Without INDEX
-- ----------------------------
mysql> EXPLAIN SELECT * FROM test2 ORDER BY value LIMIT 1\G
*************************** 1. row *************************
id: 1
select_type: SIMPLE
table: test2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3
Extra: Using filesort
1 row in set (0.00 sec)
MySQL проверил 3 строки, чтобы вывести результат. После CREATE INDEX получаем следующее:
mysql> CREATE INDEX ix_value ON test2 (value) USING BTREE;
Query OK, 0 rows affected (0.14 sec)
-- ----------------------------
-- With INDEX
-- ----------------------------
mysql> EXPLAIN SELECT * FROM test2 ORDER BY value LIMIT 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test2
type: index
possible_keys: NULL
key: ix_value
key_len: 32
ref: NULL
rows: 1
Extra: Using index
1 row in set (0.00 sec)
Теперь MySQL использовал только 1 строку.
Отвечая на полученные комментарии, я попробовал тот же запрос без LIMIT:
-- ----------------------------
-- Without INDEX
-- ----------------------------
mysql> EXPLAIN SELECT * FROM test2 ORDER BY value\G
*************************** 1. row ******************
id: 1
select_type: SIMPLE
table: test2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3
Extra: Using filesort
-- ----------------------------
-- With INDEX
-- ----------------------------
mysql> EXPLAIN SELECT * FROM test2 ORDER BY value\G
*************************** 1. row *****************
id: 1
select_type: SIMPLE
table: test2
type: index
possible_keys: NULL
key: ix_value
key_len: 32
ref: NULL
rows: 3
Extra: Using index
Как видим, для 2-го ORDER BY
используется индекс.
Чтобы создать индекс для вашего поля, используйте это:
CREATE INDEX ix_comment_time ON tbl_comments (comment_time) USING BTREE;
http://dev.mysql.com/doc/refman/5.0/en/create-index.html
LIMIT
. Если вы хотите заказать всю таблицу, индекс использовать нельзя. MySQL может выбрать (с умом) получение всей таблицы и сортировку.
- person ypercubeᵀᴹ; 28.05.2013
99901 rows in set (1.72 sec)
. Затем вышел из mysql, затем вошел в систему, сделал RESET QUERY CACHE;
, затем создал index. 99901 ряд в наборе (0,34 сек). Почему?
- person user4035; 28.05.2013
Индекс в поле comment_time
может вообще не помочь для такого запроса:
SELECT *
FROM tbl_comments
WHERE id=222
ORDER BY comment_time;
Запросу необходимо просканировать таблицу, чтобы найти совпадающие id
значения. Это можно сделать путем сканирования индекса, поиска строк и выполнения теста. Если есть одна строка, которая совпадает, и у нее самый высокий текст comment_time
, то это требует сканирования индекса и чтения таблицы.
Без индекса он просканирует таблицу, найдет строку и очень быстро отсортирует первую строку. Последовательное сканирование таблицы обычно будет быстрее, чем сканирование индекса с последующим поиском страницы (и определенно будет быстрее для таблицы, размер которой превышает доступную память).
С другой стороны, индекс на id, comment_time
был бы очень полезен.
Технически вам не нужны индексы для каждого поля, так как это тоже будет работать, однако по соображениям производительности вам может понадобиться один или несколько.
ИЗМЕНИТЬ
Эта проблема известна с самого начала разработки программного обеспечения. Обычно, если вы увеличиваете объем памяти, используемый программой, вы уменьшаете ее скорость (при условии, что программа хорошо написана). Назначение индекса полю увеличивает объем данных, используемых базой данных, но ускоряет поиск. Если вы не хотите ничего искать по этому полю (вы действительно делаете это в вопросе), в этом нет необходимости.
В современную эпоху индексы не такие большие по сравнению с размером данных на диске, и добавление одного или нескольких не должно быть плохой идеей.
Обычно очень сложно с уверенностью сказать, нужен ли мне индекс или нет. Некоторая помощь предоставляется оператором EXPLAIN
(см. Руководство).
comment_time
, улучшит ли это производительность?
- person Munib; 28.05.2013
Что касается вашего первого вопроса, вам не нужно создавать индекс для comment_time. Если количество записей очень велико, вам понадобятся индексы для ускорения поиска. Но для работы вам не нужны индексы. Для вашего второго вопроса вам поможет использование такого предложения WHERE.
WHERE(comment_time BETWEEN 'startDate' AND 'endDate');
Вам не нужно помещать индекс в comment_time, если ваш where id отличается.
Чтобы увеличить скорость поиска данных, вам понадобится index. Это также будет работать без индекса. Для вашего второго вопроса вы можете использовать предложения WHERE
и BETWEEN
.
См .: http://www.w3schools.com/sql/sql_between.asp
Оператор EXPLAIN очень полезен в подобных ситуациях. Для вашего запроса вы должны использовать его следующим образом:
EXPLAIN SELECT * FROM tbl_comments WHERE id=222 ORDER BY comment_time
Это выведет, какие индексы используются для выполнения запроса, и позволит вам проводить эксперименты с разными индексами, чтобы найти лучшую конфигурацию. Чтобы ускорить сортировку, вам понадобится индекс BTREE, поскольку он хранит данные в отсортированном виде. Чтобы ускорить поиск элементов с определенным идентификатором, лучше использовать индекс HASH, поскольку он обеспечивает быстрый поиск предикатов равенства. Обратите внимание, что MySQL может не использовать комбинацию обоих индексов для выполнения вашего запроса и вместо этого будет использовать только один из них.
Дополнительная информация: http://dev.mysql.com/doc/refman/5.7/en/using-explain.html
Для предикатов диапазона, например дат в диапазоне дат, индекс BTREE будет работать лучше, чем индекс HASH.
Дополнительная информация: http://dev.mysql.com/doc/refman/5.7/en/create-index.html