Как оптимизировать запрос к MySQL ( 200 000 записей)

[general]

Как оптимизировать запрос к MySQL ( 200 000 записей)

Встала проблема, из-за которой провайдер нас гонит на COLLOCATION - мол только на отдельном сервере должна быть такая нагрузка.

Помогите, как оптимизировать. Может быть дело во внешних атаках на сайт.


Есть сайт, создаваемый на лету. В базе 200 000 записей. Полей всего 5. Есть и

Для построения каталога мы производим выборку, сортируя таблицу по разным полям. Ставим лимит примерно в 80-т записей.

В секунду иногда бывает от 1 до 30 запросов типа:

SELECT * FROM table ORDER BY name ASC LIMIT 119191, 119277
SELECT * FROM table ORDER BY surname ASC LIMIT 163126, 163212
SELECT * FROM table ORDER BY surname DESC LIMIT 92743, 92829


Таблица выглядит так:

CREATE TABLE `table` (
`id` mediumint(9) NOT NULL default '0',
`category` mediumint(9) default '0',
`surname` varchar(255) default NULL,
`name` varchar(255) default NULL,
`personal` varchar(255) default NULL,
`date` mediumint(9) default '0',
PRIMARY KEY (`id`)
) TYPE=MyISAM;




Провайдер говорит, что нагрузка на MySQL критическая и закрывает базу.

Что делать?

 

[Dreammaker]

По идее идёт фулскан таблицы каждый раз.

Попробовать поставить индексы на surname и name. Возможно поставить по началу столбцов.

Правда, если апдейтов и вставок много может наоборот замедлить таблицу.

 

[confguru]

EXPLAIN что говорит?

 

[Фанат]

`date` mediumint(9)

это сильно

 

[PartizaneN]

Re: Как оптимизировать запрос к MySQL ( 200 000 записей)

Автор оригинала: general
Для построения каталога мы производим выборку, сортируя таблицу по разным полям. Ставим лимит примерно в 80-т записей.

В секунду иногда бывает от 1 до 30 запросов типа:

SELECT * FROM table ORDER BY name ASC LIMIT 119191, 119277
SELECT * FROM table ORDER BY surname ASC LIMIT 163126, 163212
SELECT * FROM table ORDER BY surname DESC LIMIT 92743, 92829

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

Посмотри в общем на свои лимиты... Как я полагаю нужно 80 (или в районе 80) записей выбрать, а здесь, например, 92829 записей начиная с 92743....

 

[Фанат]

виноват, был неправ.

 

[shefeduard]

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

 

[ys]

Re: Как оптимизировать запрос к MySQL ( 200 000 записей)

Автор оригинала: general
Встала проблема, из-за которой провайдер нас гонит на COLLOCATION - мол только на отдельном сервере должна быть такая нагрузка.
.....
Что делать?

У меня тут возникла больная мысль, ибо если: "провайдер нас гонит на COLLOCATION".

Сказать, что: "спасибо, но COLLOCATION мы возьмем в другом месте".

Должно помочь...

 

[baev]

ys, shefeduard вы б ответ PartizaneN повнимательнее прочитали, а?

 

[ys]

baev

Да я понял, что надо человеку про "LIMIT" почитать,
но я не про то, а про первую строку.

 

[baev]

про первую строку

И?
Типа, найдётся дурачок-провайдер, который будет спокойно смотреть на результат вот этого:

В секунду иногда бывает от 1 до 30 запросов типа:

SELECT * FROM table ORDER BY name ASC LIMIT 119191, 119277
SELECT * FROM table ORDER BY surname ASC LIMIT 163126, 163212
SELECT * FROM table ORDER BY surname DESC LIMIT 92743, 92829

?

 

[general]

По поводу ошибки LIMIT - спасибо. Не заметил.

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

SELECT * FROM table ORDER BY surname ASC LIMIT 163126, 163212
и
SELECT * FROM table ORDER BY surname ASC LIMIT 163126, 80


Время ОДИНАКОВОЕ! от 6 до 9 секунд. Причем с "правильными" лимитом также бывает 9 секунд. Можно ли предположить, что нагрузка на базу при исполнении этих запросов также одинаковая? И этим исправлением - не поможешь?

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

Что еще посоветуете?

 

[SelenIT]

Уже советовали - см. пост Dreammakerа.

 

[general]

EXPLAIN SELECT выдает

table ALL 200000 Using filesort

 

[baev]

Попробовать поставить индексы на surname и name

(соответственно: смотрим EXPLAIN для каждого варианта...)

 

[Фанат]

general
объясни, зчем тебе такие лимиты?
откуда они берутся?

 

[general]

to Фанат

Есть база личных данных - ФИО и проч.
Сейчас 200 тыс человек. Каталог людей формируется "на лету".
Причем задача - сделать его удобным - чтобы сортировка по разным основаниям была на лету.

Т.о. у нас есть страницы каталога, на которых люди сортируются по фамилии, есть - по имени и т.д.

Максимальное число людей на страницу - 80.

Отсюда и лимиты (200 тыс делим на 80).

 

[Фанат]

покажи мне идиота, который будет листать твой каталог на 1000 страниц.
после этого продолжим

 

[general]

to Фанат
ежедневно 1000-1200 таких "идиотов" + поисковики

to Solid
Фанат разве женского пола?

 

[alexhemp]

general

Сделай нормализацию данных.

surname` varchar(255) default NULL,
`name` varchar(255) default NULL,
`personal` varchar(255) default NULL,

Вот эти поля в 3 отдельные таблицы, ссылки на них в основную. Тогда длина строки основной таблицы будет константной это может помочь LIMIT-у.