Как сделать быструю сортировку в MySQL?

[boric]

Что-то я туплю наверно. В общем проблема в следующем.
Например есть таблица книг и таблица авторов:

CREATE TABLE  book (
  id int(11) NOT NULL AUTO_INCREMENT,
  pos decimal(6,2) NOT NULL,
  name varchar(255) NOT NULL,
  author_id int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `pos` (`pos`),
  KEY `author_id` (`author_id`)
);

CREATE TABLE  author (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
);

Делаю запрос:

SELECT book.name, author.name 
FROM book INNER JOIN author ON (book.author_id = author.id) 
WHERE book.pos > 10 
ORDER BY book.pos ASC

Т.е. банальная выборка книг с именами авторов с позициями больше 10, отсортированная по возрастанию позиций.

Проблема в том, что при сортировке по полю 'pos' не используется индекс - explain показывает "using filesort". Если не присоединять таблицу авторов, то все нормально, индекс используется.

Как-нибудь можно в подобных банальных ситуациях задействовать при сортировке индекс?

[eresik]

Никак.
MySql не использует сортировку по индексу при JOIN-ах

[boric]

eresik,

Никак

Тогда может быть в таких случаях вообще отказаться от сортировки и делать ее средствами пхп (питона и пр.)? А то ведь там кроме filesort ище и временная таблица создается. В любом случае придется это учитывать.

PS:
Щас еще потестил. Вроде как если в таблице book индекс поля author_id сделать уникальным, то сортировка по индексу срабатывает. Но в данном примере это не актуально.

[Йода]

Сообщение от boric

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

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

Но это мне так чуйка подсказывает. А как на самом деле- хз- пробовать надо и замерять..

[eresik]

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

[boric]

Йода,

Наверное дешевле считать авторов в массив и потом его приклепать к считанной и сортированной таблице книг

Можно и так, особенно если табица авторов небольшая.

eresik,

На PHP что-то "прикручивать" в любом случае однозначно дороже будет.

Надо конечно проверять, но например при результирующей выборке 10 строк, имхо, быстрее сделать быструю выборку по индексу, а для сортировки использовать функцию ПХП (Питона), написанную кажись на С, чем поручать сортировку MySQL, которая для сортировки будет создавать временную таблицу и использовать filesort.

Йода
Конкретно сейчас у меня проблема обнаружилась в определении "предыдущего" и "следующего" узла в динамически-отсортированном списке. Для этого я делал запрос с выборкой элементов с position больше (меньше), чем у текущего элемента, с сортировкой по полю position и limit 1. В джанго я использовал select_related(), который во втором джоине тянул авторов (а этот второй джоин и портил сортировку), причем авторы мне на этом шаге были не нужны. Я тогда убрал второй джоин, сделав select_related(depth=1). Веременные таблицы и filesort убрались. И о чудо - скорость открытия таких страниц возрасла почти в два раза. На одиночных запросах разница не заметна, а вот когда 100 человек одновременно сайт просматривают, разница оказалась существенной, особенно там, где у элемента соседей много.

[eresik]

Сообщение от boric

Надо конечно проверять

Ни разу не сталкивался, чтоб на стороне скрипта что-то выполнилось быстрее, чем если ту же работу поручить MySQL (хотя наверное всяко бывает)

[boric]

eresik,

Ни разу не сталкивался, чтоб на стороне скрипта что-то выполнилось быстрее

А я постоянно так делаю. Если получается сложный запрос, я его упрощаю переносом части логики в скрипт. Или если получается много мелких запросов, я обычно делаю одним простым запросом выборку всех необходимых данных и уже в скрипте их обрабатываю. Работа с ассоциативными массивами и с встроенными функциями происходит очень быстро, т.к. многие библиотечные функции написаны на Си. Также сделать сотню итераций в цикле (в ОЗУ) будет быстрее, чем делать даже маленький запрос к БД. Например предложение
Йода предварительно загрузить авторов в хэш будет работать очень быстро: к автору можно будет очень быстро обращаться $autors[$author_id]->name. Но конечно надо баланс соблюдать.