стоит ли ставить индекс для часто обновляемого поля?

que_bunt · 2 Май 2009

стоит ли ставить индекс для часто обновляемого поля?

здравствуйте.

в результате профайлинга скрипта появился вопрос.
скрипт исполняется 0.5с, из них 0.2с основной код - это очень хорошо,

но 0.3с исполняется код получения "наиболее просматриваемых", "наиболее комментируемых" и "пользователей с наибольшим количеством баллов"

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

правильно ли я понимаю что если я поставлю индекс на это поле то выборка будет идти быстрее, но обновление поля и соответственно перестройка индекса будет забирать еще больше ресурсов сервера?

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

буду благодарен за конструктивный ответ.

Alexandre · 2 Май 2009

правильно ли я понимаю что если я поставлю индекс на это поле то выборка будет идти быстрее, но обновление поля и соответственно перестройка индекса будет забирать еще больше ресурсов сервера?

а что мешает проверить? сделай замер с и без индекса

weregod · 3 Май 2009

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

Raziel[SD] · 4 Май 2009

Мне почему-то кажется, что выборки происходят гораздо чаще, чем добавления, поэтому имеет смысл бороться за скорость выборок

З.Ы. а такие данные "наиболее просматриваемых", "наиболее комментируемых" и "пользователей с наибольшим количеством баллов" выгоднее по крону пересчитывать(например раз в n минут), чем при каждом запросе.

Фанат · 4 Май 2009

обычно так и бывает. но стоит обратить внимание на вопрос

que_bunt · 4 Май 2009

Alexandre, сделал замер - время исполнения скрипта уменьшается, но незначительно;
я просто думал что перестройка индекса mysql нагружает сервер в фоне, и соответственно хоть время исполнения скрипта и уменьшается но нагрузка на сервер на самом деле увеличивается, или я ошибаюсь?

weregod, Raziel[SD] спасибо за совет, видно действительно нужно идти в эту сторону.

Фaнат, если я в корне не правильно ставлю вопрос то, то буду благодарен если обьяснишь как мне стоит его ставить.

prolis · 4 Май 2009

que_bunt
[telepat mode on]
1. Архитектура. Скорее всего индекс по одному этому полю оказался не достаточно селективным, потому что большая часть значений сосредоточена в нескольких массивах. - нужно создать составной индекс по нескольким полям, участвующим в запросе и смотреть план.
2.Бизнес-логика. "наиболее просматриваемых", "наиболее комментируемых" на практике не интересно. В условия можно добавить типа "за последнюю неделю с момента последнего обновления" - и включить поле последнего обновления в вышеприведенный индекс.

que_bunt · 4 Май 2009

prolis, спасибо! у тебя то еще тот telepat mode

1. листая темы про mysql как раз прочитал про составной индекс, счас буду эксперементировать.
и вопрос на будующее: как узнать что я не перегнул с количеством индексов? у меня счас индексы на всех полях по которых делаются выборки, и ситуация такая что из 10 полей индексы на 5 полях.

2. я чесно говоря не понял о чем ты,
"наиболее просматриваемые" и "наиболее комментируемые" - на самом деле выборка идет за последнии 30 дней ( add_datetime<NOW() AND (TO_DAYS(NOW()) - TO_DAYS(add_datetime) <= 30) )

поле "с момента последнего обновления" - этого я как раз не понимаю, откуда это поле?

prolis · 4 Май 2009

que_bunt
1.С индексами не перебарщивай, одного праймари и одного составного для выборок должно хватать. Мы тут слово "план" употребляем, это http://www.mysql.ru/docs/man/EXPLAIN.html - ориентируйся на него. Приведи примеры запросов и планы этих долгих запросов для выключения телепатии.
Лично я стараюсь лишний раз лучше не достроить индекс, чем держать редко используемый.
2. В данном примере индекс не используется, потому все значения итак add_datetime<NOW() , а индекса по функции TO_DAYS в бд нет. Как одной функцией выбрать дату минус 30 дней посмотри на форуме.

fixxxer · 4 Май 2009

http://dev.mysql.com/doc/refman/5.1/en/optimization.html

prolis · 4 Май 2009

на оригинальном ресурсе пример всё-таки такой:
[sql]
SELECT something FROM tbl_name
WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_col
[/sql]

Alexandre · 4 Май 2009

сделал замер - время исполнения скрипта уменьшается, но незначительно;

как делал замер?

que_bunt · 4 Май 2009

prolis разберем один запрос, думаю с остальными разберусь по аналогии.

"наиболее комментируемые за месяц"

[sql]
EXPLAIN SELECT *
FROM `answers`
WHERE parent_id = '0'
AND add_datetime < NOW( )
AND (
DATE_SUB( CURDATE( ) , INTERVAL 30
DAY ) <= add_datetime
)
ORDER BY answers_count
LIMIT 0 , 5
[/sql]

Код:

id 	               => 1
select_type 	       => SIMPLE
table 	               => answers
type 	               => range
possible_keys 	       => parent_id,add_datetime,parent_id_2
key 	               => parent_id_2
key_len 	       => 12
ref 	               => NULL
rows 	               => 235
Extra                  => Using where; Using filesort

-~{}~ 04.05.09 17:46:

индексы у меня такие:
PRIMARY, parent_id, add_datetime, user_id, answers_count, parent_id_2 (составной parent_id+add_datetime)

add_datetime < NOW( ) - нужно, так как есть запись с датой добавления в будующем.

TO_DAYS(NOW()) - TO_DAYS(add_datetime) <= 30 - это я кстати взял с мана (mysql.ru/docs/man/Date_and_time_functions.html)

prolis · 5 Май 2009

que_bunt
1. add_datetime < NOW( ) - это конечно не влияет ни на что, но в нашем мире ещё машину времени не изобрели, так что можно не заморачиваться
2. Теперь самое интересное - расшифровка плана
http://www.mysql.ru/docs/man/EXPLAIN.html
3.Обрати внимание на разницу планов при выборе записей за 30 дней при старом и новом выражении в where.
4. про ман я догадался, поэтому то и заподозрил что что-то не так, слазил на оригинальный сайт http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html
5.план получился предсказуем, если хочешь разогнать данный запрос до космических скоростей обрати внимание на Using filesort
6.[telepat mode on] в поле parent_id у тебя наверняка крайне мало уникальных значений, а оно состоит в двух индексах - много чести. Если убить эти ключи, план поменяется, а время запроса почти нет [telepat mode off]

que_bunt · 5 Май 2009

Alexandre на локалхосте xdebug, на сервере microtime

prolis благодарю за ответы,
1. с этим заморачиватся нужно, више писал что: есть записи с датой добавления в будующем, тоесть они уже в базе но выводить их пока не настанет "add_datetime < NOW( )" нельзя.

2. спасибо за ссылку, 3 раза прочитал, оно как бы понятно, но не все.

я понял что key это используемый индекс, rows количество строк которые мускуль должен прочитать чтобы сделать выборку по даному запросу при использовании даного индекса, и также скорость выполения запроса зависит от того есть ли в Extra Using where; Using filesort.

но там также сказано "При помощи EXPLAIN можно выяснить, когда стоит снабдить таблицы индексами", я пока не выяснил как это делать.

3. prolis, но разницы в планах нет, планы идентичны! (либо я тупой).
да твоя запись намного удобнее, красивее, и понято что быстрее так как в каждой записи не нужно проделывать функцию TO_DAYS, но планы одинаковые.

5. обращаю внимание на Using filesort, но ничего не понимаю,
у меня же ORDER BY answers_count и есть индекс по полю answers_count, значит Using filesort не должны бы быть, насколько я понимаю.

6. "крайне мало уникальных значений" - в среднем 13-15% уникальных значений.

-~{}~ 05.05.09 15:22:

я кстати разобрался с "пользователи с наибольшим количеством баллов"
(SELECT * FROM users ORDER BY points DESC LIMIT 0 , 10)
план показал что type=ALL (в мане говорится что это плохо) + Using filesort
я сделал индекс по полю points и план стал получше type=index, key=point, extra пустой
правда мускуль как просматривал все имеющиеся rows так и просматривает, но тут ничего не сделаешь, таков запрос.

и получилось как ты и говорил 2 индекса в таблице: primary и points.

Wicked · 5 Май 2009

5. обращаю внимание на Using filesort, но ничего не понимаю,
у меня же ORDER BY answers_count и есть индекс по полю answers_count, значит Using filesort не должны бы быть, насколько я понимаю.

после того, как mysql взял в оборот индекс parent_id_2, остальные индексы его мало волнуют, потому что локализовать записи или использовать их для сортировки обычно не представляется возможным

que_bunt · 5 Май 2009

а как же тогда "разогнать данный запрос до космических скоростей" ? (или хотябы просто немного оптимизировать)
пробовал добавить answers_count в составной индекс parent_id_2, явно не то %(

Wicked · 5 Май 2009

а что, filesort на 200 записях сильно тормозит?

для начала я бы избавился от NOW() и CURDATE( ) в запросе, чтобы заработал query cache, и избавился от ежесекундной изменчивости запроса: я думаю, если генерить now() как date('Y-m-d H:i:00'), то никто не умрет, а кэш будет служить лучше

-~{}~ 05.05.09 20:12:

"SELECT *" - так ли оно тебе нужно?

-~{}~ 05.05.09 20:20:

еще можно сделать денормализованный флажок актуальности даты - да/нет.
тогда можно поиметь индекс (parent_id, is_actual_date, answers_count), что даст нам в explain'e "type = const" и красивую пустоту в extra.

-~{}~ 05.05.09 20:22:

и запрос будет выглядеть так:
[sql]EXPLAIN SELECT *
FROM `answers`
WHERE parent_id = '0' AND is_actual_date
ORDER BY answers_count
LIMIT 0 , 5[/sql]

prolis · 5 Май 2009

que_bunt
1. (вот я настырный)[sql]AND add_datetime between NOW( ) AND (
DATE_SUB( CURDATE( ) , INTERVAL 30 DAY )[/sql] - никакой разницы в плане, но мне эстетичнее
2. В плане смотрится сначала на тип (поле type) связывания, смотри там комментарий к типу ALL. Необходимость индекса зачастую сводится к уменьшению таких в запросе. Изучать план на основе запроса к одной таблице - толку не много, пойдешь дальше - увидишь пользы больше.
3.Или я неправ, или ты, или оптимизатор

В оракле пришлось бы строить хитрый индекс по такой функции.
6. Ну для теста убей составной индекс и померь время.
7. А сравни [sql]
SELECT * FROM users ORDER BY points DESC LIMIT 0 , 10
и
SELECT points FROM users ORDER BY points DESC LIMIT 0 , 10
[/sql]
ALL на самом деле не всегда плохо, особенно на малых табличках, когда у тебя юзеров будет много - план изменится

que_bunt · 5 Май 2009

Wicked now() исправил, прочитал про query cache,
а CURDATE будет влиять на query cache? он же в течении для одинаковый, или все же лучше генерировать в php?

кроме того о query cache вообще: в таблице минимум раз в минуту обновляется поле views (количество просмотров), насколько я понял из мана в таком случае кеш постоянно будет збрасыватся и значит это ничего не дает. ?

"SELECT *" - нужно минимум 5 из 15 полей в таблице, врятли разница почуствуется, но теперь буду иметь и это ввиду.

-~{}~ 05.05.09 17:04:

prolis

1. согласен )

6. счас пробую

7. да, быстрее на 24% (даные пхпмайадмина), но мне нужно больше полей в любом случае.

-~{}~ 05.05.09 17:07:

а что, filesort на 200 записях сильно тормозит?

Wicked, пока это 1 из 2 самых тормозных запросов в скрипте, и думаю что когда через 3-4 месяца из 200 станет 2000, то будет еще хуже.

стоит ли ставить индекс для часто обновляемого поля?

Новичок

PHPПенсионер

unserializer

untitled00

oncle terrible

Новичок

Новичок

Новичок

Новичок

К.О.

Новичок

PHPПенсионер

Новичок

Новичок

Новичок

Новичок

Новичок

Новичок

Новичок

Новичок