здравствуйте,
Прошу помощи. Вроде бы не первый раз с БД работаю, но как-то давно не сталкивался с классикой и застрял.
Мускул 5.1.57 если что, таблицы myisam (нужен фултекст, так что без вариантов, наверное)

Классическая казалось бы задача

Таблица categories_rel - связь между категориями и товарами. около 12млн записей.
уникальных catid около 60 тысяч
catid, goodid
оба поля unsigned int (11), висит индекс на одном поле, на другом поле и даже висел на обоих

goods - товары. около 1млн записей
goodid - unsigned int (11), первичный ключ, т.е. понятно что с индексом
orderid - unsigned int (11), висит индекс, для сортировки вывода
datagood text - висит фултекст, собственно данные по товару
и еще несколько цифровых полей

Стандартная задача - выбрать все товары из одной категории, отсортировать по orderid Полагал могу рассчитывать на время порядка 0.01 или лучше. Фигу.
Вариант 1


Выполняется за 0.3 секунды. Всего 60к+ записей выдает
По профилированию 0.22 из них это Copying to tmp table. И0.02 этоSorting result
По эксплеину
1 SIMPLE b ref catid,goodid catid 4 const 93574 Using where; Using temporary; Using filesort
1 SIMPLE a eq_ref PRIMARY PRIMARY 4 x_base.b.goodid 1
Если выбирать только goods.id (но datagood тоже нужен) получается 0.2 секунды, в принципе это быстрее в итоге (довыбрать еще 1 запросом остатки данных), но недостаточно.

Вариант 1.1.
То же что вариант 1, но перевернутый - беру таблицу связей к ней джоиню таблицу товаров и сортирую. Получается на 10% дольше, все остальные симптомы те же. По правильному вроде джоинить к той таблице по которой идет сортировка, так что.

Вариант 1.2.
То же что вариант 1, но без сортировки. С практической точки зрения это абсолютно не юзабельно, но для данных и анализа пусть будет.
Выполняется за 0.1 секунды. Из них все 0.1 секунды это Sending data.
По эксплеину
SIMPLE b ref catid,goodid catid 4 const 93574 Using where
1 SIMPLE a eq_ref PRIMARY PRIMARY 4 x_base.b.goodid 1

Вариант 1.3.
То же что вариант 1, но без лимита. Результат такой же.

Вариант 2


Выполняется 4 секунды с лимитом.
По профилированию все 4 секунды занимает preparing.
По эксплеину
1 PRIMARY goods ALL NULL NULL NULL NULL 980000 Using where; Using filesort
2 DEPENDENT SUBQUERY categories_rel index_subquery catid,goodid goodid 4 func 13 Using where
Любопытно что ВТОРОЙ запрос такой же тут же посланный выполняется за 0.0001 секунды
Тут это важно я считаю потому, что preparing занимает 4 секунды, таким образом если бы мускул каждый раз препарингом не занимался - все было бы кучеряво.
Без лимита выполняется 0.004 секунды!!!
С лимитом но без сортировки выполняется 0.27 секунды.


Что я еще попробовал.
А) Поскольку это еще и php, попробовал prepared statements, но по факту ПЛАН запроса компилируется каждый раз. Поэтому надежда составить типа шаблон для варианта 2 и потом подставлять в него разные ИД не прокатил.
Б) Поскольку от размера таблиц тоже иногда что-то зависит - попробовал вынести из goods самое жирное поле datagood, но на скорости это не сказалось от слова вообще никак


Может я не догоняю чего-то очевидного?
Казалось бы задача простейшая, веками решаемая, но какая-то ерунда.
Подскажите пожалуйста правильный вариант решения.

miksoftsgalaМускул 5.1.57 если что, таблицы myisam (нужен фултекст, так что без вариантов, наверное)В версиях 5.7.* и фултекст в InnoDB добавили, и убрали баг с тормозами конструкции IN (SELECT ...)К сожалению максимум на что могу апгрейднуться это 5.6:(

miksoftЕсли нет возможности обновить MySQL, то переписывайте запрос, избавляйтесь от IN (SELECT ...).Вот вопрос как переписать.
Посмотрите пожалуйста мой "вариант 1" , он без "селект ин", в принципе я с него и начал, но я категорически не понимаю почему он так много времени занимает - 0.3с.
Джоин по индексам должен был бы быть быстрым, а сортировка опять же идет по индексированному полю первой таблицы джоина - по манам как я понял индекс зацеплятся должен и мне почему-то кажется что раньше так не тормозило.
Что может быть не так?
Или в принципе это неверная идея запроса?

вадяавторК сожалению максимум на что могу апгрейднуться это 5.6:(
странное ограничениеконтрольная панель выше 5.6 не поддерживает, плюс некоторая универсальность должна быть.
в общем апгрейд не вариант.

поэтому или надо разбираться почему тормозит 1 вариант или придумывать 3-тий

Любопытно, поставил 5.6.
На 5.1 работал запрос


отрабатывал за 4с
на 5.6 такой запрос возвращает ошибку (1054 - Неизвестный столбец 'SQL_NO_CACHE' в 'field list')
ну а


отрабатывает за 0.3 секунды, что ЗНАЧИТЕЛЬНО лучше чем 4 секунды на 5.1.
По профайлингу получается 0.2 секунды из 0.3 это "sending data".
explain тоже другой
1 SIMPLE <subquery2> ALL NULL NULL NULL NULL NULL Using temporary; Using filesort
1 SIMPLE goods eq_ref PRIMARY PRIMARY 4 <subquery2>.qid 1 NULL
2 MATERIALIZED categories_rel ref catid,goodid catid 4 const 45180 NULL


Это значит в 5.6 этот баг уже поправили?

К сожалению 0.3 секунды не вариант:( Нужно на порядок меньше.

miksoftsgalaна 5.6 такой запрос возвращает ошибку (1054 - Неизвестный столбец 'SQL_NO_CACHE' в 'field list')Хинт в подзапросе и не нужен. Странно, что оно в 5.1 работало.
sgalaЭто значит в 5.6 этот баг уже поправили?Да, точно, в 5.6 поправили.кстати фултекст тоже в 5.6 появился походу.
Попробовал пока походу innoDB вместо myisam - запрос с джоинами остался тем же по времени, запрос с вложенным селектом стал дольше - вместо 0.3 стал минимум 0.6. Печаль, иннодб в сад в данном случае.

И все же проблема остается . 0.3 секунды как по мне так это очень много. Даже в 5.6 где баг с вложенным селектом исправили - получается в лучшем случае 0.3... Да и джоины из 1 варианта те же 0.3 секунды давали.

Если выбирать только ID товаров (для последующей выборки уже по ИД), то запрос сокращается до 0.2 секунды что уже лучше (на выборку товаров по ИД уходит 0.01 секунда потом), но во-первых это все равно много. А во вторых это какой-то бред что возврат данных из мускула с последующим запросом в него же занимает меньше времени чем если делать все внутри сразу.

Неужели нет какого-то 3 варианта? Или более шустрой реализации первых двух?
Я же вижу на нормальных сайтах у конкурентов выборка по категориям ну никак 0.3 секунды не занимает, учитывая то что там весь сайт грузится за 0.05, а тут жесть какая-то с тормозами. Я правильно все делаю? Или все же есть более другой способ?

miksoftПредлагаю эксперимент:
1) Создайте два индекса (goodid,orderid) и (orderid,goodid)
2) Выполните ANALYZE TABLE для goods и categories_rel
3) Попробуйте такой запрос:
1) При "составном" индексе запрос ускоряется на 10% по сравнению с обычным индексом, ну т.е. 0.28 вместо 0.3. При этом нет разницы в какую сторону строить.
2) Уже все ужато до минимума, по аналайзу и подправлял поля.
3) Да, пробовал, это как раз тот что я описывал как
sgalaЕсли выбирать только ID товаров (для последующей выборки уже по ИД), то запрос сокращается до 0.2 секунды что уже лучше (на выборку товаров по ИД уходит 0.01 секунда потом), но во-первых это все равно много. А во вторых это какой-то бред что возврат данных из мускула с последующим запросом в него же занимает меньше времени чем если делать все внутри сразу.

еще вопрос . напомните пожалуйста. есть что-то в mysql для углубленного анализа выполнения запроса?
я имею ввиду что бы понять влезла ли КОНКРЕТНАЯ выборка во временную таблицу в памяти или свалилась на диск и так далее? т.е. полная последовательность того чего мускул делает с запросом и как? не просто эксплеин.

miksoftsgalaна нормальных сайтах у конкурентов выборка по категориям ну никак 0.3 секунды не занимает,Мы же не знаем какая там структура БД.
Возможно, они используют денормализацию или еще что-то.я не против изменить структуру:) просто непонятно как.
в категории может быть до 300к товаров, так что совать их в текстовое поле к категории это жестоко и явно бессмысленно.
а других идей пока нет, many2many вроде же классика.
да и сам факт меня вымораживает долгой работы простого запроса - не должно быть так.

miksoftsgala,

Еще хорошо посмотреть бы, что у вас с размером всяких буферов.
В т.ч. c tmp_table_size и max_heap_table_size.16мб и то и другое.

miksoftsgalaя не против изменить структуру:) просто непонятно как.Например, можно создать копию поля orderid в таблице categories_rel. И для сортировки использовать именно его.Тут нюанс, полей для сортировки не одно, а около 4 штук, к текущим 12млн записей добавить еще 48 мегабайт - боюсь что таблица будет читаться еще дольше, а уж как там индексы будут ворочаться - страшно подумать.
Т.е. как я понимаю основной тормоз сейчас в том, что мускулу из 12млн записей нужно выдернуть нужные что бы составить таблицу для сортировки (раз уж он индексы использовать по какой-то загадочной причине не хочет, хотя должен бы судя по мануалу), а они отнюдь не подряд лежат. Если же таблица раздуется еще на 48мегабайт, то это "Не подряд лежат" увеличится еще в разы.
Но попробовать пожалуй стоит, спасибо.

miksoftsgalaв категории может быть до 300к товаровИ их все кто-то будет постранично листать по 50 позиций???Дело в том, что даже лимит 0,50 ситуацию не улучшает. Т.е. разница между лимит 0,50 и лимит 40000,50 около 10% по скорости. Если бы затык был именно в последних страницах я бы что-то подумал на эту тему.

miksoftsgala еще вопрос . напомните пожалуйста. есть что-то в mysql для углубленного анализа выполнения запроса?
я имею ввиду что бы понять влезла ли КОНКРЕТНАЯ выборка во временную таблицу в памяти или свалилась на диск и так далее? т.е. полная последовательность того чего мускул делает с запросом и как? не просто эксплеин.Ну есть еще профилирование, но там тоже не очень-то подробная информация.
http://dev.mysql.com/doc/refman/5.7/en/show-profile.html
http://www.sql.ru/forum/698050/faq-mysql-profiler-knutom-ili-pryanikom

И есть Performance Schema
(не пользовался, подробностей не знаю).Ага, попробую, спасибо.

miksoftsgalaпропущено...
16мб и то и другое.Я бы предложил попробовать их резко увеличить, например, до 1 ГБ. Если, конечно, оперативка позволяет. Но тут такой масштаб, что оперативку экономить не приходится.Не помогло.

miksoftsgalaбоюсь что таблица будет читаться еще дольшеТут надо наращивать innodb_buffer_pool_size (если таблицы InnoDB), чтобы и таблицы, и все индексы полностью в нем размещались.
Если таблицы в MyISAM, то наращивать надо key_buffer_size, но он помещает в себя только индексы.
Тогда данные будут в оперативной памяти и физический ввод-вывод не понадобится.myisam, да
key buffer size 1024М

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


отрабатывает те самые блин самые толстые 0.22 секунды. При чем в эксплеине файл-сорт опять.
Не поверив своим глазам пошел проверять есть ли индекс - есть курилка.
Да как так-то? Я какую-то базовую вещь забыл?
Почему индекс при сортировке не используется даже тупо напрямую?

miksoftsgalaПри чем в эксплеине файл-сорт опять.А покажите целиком.

ANALYZE TABLE точно делали?я кажется нашел причину в мане, что логично, но что делать с этим в большой перспективе пока неясно, вот тут мне нужна помощь


будет за 0.22 с файлсортом, т.к. индекс для ордеринга включается только если (фанфары) выбираются записи из первой трети.
а поскольку там 60к записей с лишним, то 40к заплевывает в оставшиеся 70% и пролетайло получается
для


индекс работает и срабатывает за 0.05 вместо 0.22
и при


индекс тоже работает и тоже 0.05 вместо 0.22 получается

НО!
я уже перепробовал с десяток разных вариантов, но как это вкрячить в запрос


категорически не понимаю:( что бы именно для ордера и все такое.
Есть какие-то идеи?


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

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

индекс не включается