Запрос: SELECT psname,category,city FROM base WHERE category='1' ORDER BY date DESC LIMIT 0, 20
Время выполнения: 0,15 сек.

Запрос: SELECT psname,category,city FROM base WHERE category='1' LIMIT 0, 20
Время выполнения: 0,0015 сек.

Формат поля date: `date` int(11) NOT NULL default '0',
Проиндексировано: KEY `date` (`date`)
Наполнение: время Unix (11122222222, ...)

Как можно оптимизировать ORDER BY date DESC?

Уйти от него нельзя, очень нужно. Я пробовал увеличивать значение sort_buffer и record_buffer, как советуют в мане, но не помагает.

EXPLAIN SELECT psname,category,city FROM base WHERE category='1' ORDER BY date DESC LIMIT 0, 20

выдает:

table type possible_keys key key_len ref rows Extra
base ref category category 4 const 8998 Using where; Using filesort

Спасибо!

Цитата

.2.7. Как MySQL оптимизирует ORDER BY В некоторых случаях MySQL может использовать индекс, чтобы выполнить запрос ORDER BY или GROUP BY без выполнения дополнительной сортировки. Индекс может также использоваться и тогда, когда предложение ORDER BY не соответствует индексу в точности, если все неиспользуемые части индекса и все столбцы, не указанные в ORDER BY - константы в выражении WHERE. Следующие запросы будут использовать индекс, чтобы выполнить ORDER BY / GROUP BY. SELECT * FROM t1 ORDER BY key_part1,key_part2,... SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2 SELECT * FROM t1 WHERE key_part1=constant GROUP BY key_part2 SELECT * FROM t1 ORDER BY key_part1 DESC,key_part2 DESC SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC,key_part2 DESC Ниже приведены некоторые случаи, когда MySQL не может использовать индексы, чтобы выполнить ORDER BY (обратите внимание, что MySQL тем не менее будет использовать индексы, чтобы найти строки, соответствующие выражению WHERE): Сортировка ORDER BY делается по нескольким ключам: SELECT * FROM t1 ORDER BY key1,key2 Сортировка ORDER BY делается, при использовании непоследовательных частей ключа: SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2 Смешиваются ASC и DESC. SELECT * FROM t1 ORDER BY key_part1 DESC,key_part2 ASC Для выборки строк и для сортировки ORDER BY используются разные ключи: SELECT * FROM t1 WHERE key2=constant ORDER BY key1 Связываются несколько таблиц, и столбцы, по которым делается сортировка ORDER BY, относятся не только к первой неконстантной (const) таблице, используемой для выборки строк (это первая таблица в выводе EXPLAIN, в которой не используется константный, const, метод выборки строк). Имеются различные выражения ORDER BY и GROUP BY. Используемый индекс таблицы имеет такой тип, который не обеспечивает сортированного хранения строк (как индекс HASH в таблицах HEAP). В тех случаях, когда MySQL должен сортировать результат, он использует следующий алгоритм: Считываются все строки согласно ключу или путем сканирования таблицы. Строки, которые не соответствует предложению WHERE, пропускаются. Ключ сортировки сохраняется в буфере сортировки (размера sort_buffer) Когда буфер заполняется, содержимое буфера сортируется алгоритмом qsort, результаты сохраняются во временном файле. Сохраняется указатель на отсортированный блок (в том случае, когда все строки умещаются в буфере сортировки, временный файл не создается). Вышеупомянутое действие повторяется, пока не будут считаны все строки. Делается мультислияние до MERGEBUFF (7) областей в один блок в другом временном файле. Это действие повторяется, пока все блоки из первого файла не окажутся во втором файле. Предыдущий пункт повторяется, пока не останется менее MERGEBUFF2 (15) блоков. При последнем мультислиянии в результирующий файл записывается только указатель на строку (последняя часть ключа сортировки). Теперь код в файле sql/records.cc будет использоваться для чтения данных в отсортированном порядке, с использованием указателей на строки из результирующего файла. Чтобы оптимизировать этот процесс, мы считываем большой блок указателей на строки, сортируем их, и затем считываем строки в отсортированном порядке в буфер строк (record_rnd_buffer). При помощи команды EXPLAIN SELECT ... ORDER BY можно проверить, может ли MySQL использовать индексы для выполнения запроса. Если в столбце extra содержится значение Using filesort, то MySQL не может использовать индексы для выполнения сортировки ORDER BY. See Раздел 5.2.1, «Синтаксис оператора EXPLAIN (получение информации о SELECT)». Чтобы сортировка ORDER BY выполнялась с большей скоростью, нужно сначала посмотреть, можно ли заставить MySQL использовать индексы взамен дополнительной фазы сортировки. Если это невозможно, то можно сделать следующее: Увеличить значение переменной sort_buffer. Увеличить значение переменной record_rnd_buffer. Изменить переменную tmpdir, чтобы она указывала на выделенный диск с большим количеством свободного пространства. Начиная с MySQL 4.1, в tmpdir могут быть указаны несколько путей, разделенных двоеточием : (точкой с запятой на Windows ;). Эти пути будут использованы в ротации. Внимание: Эти пути должны находится на разных физических дисках, не на разных разделах одного и того же диска. MySQL по умолчанию сортирует все GROUP BY x,y[,...] запросы так, как если бы вы указали ORDER BY x,y[,...]. MySQL будет оптимизировать любой ORDER BY как сказано выше, без всяких потерь производительности. Если, как в некоторых случаях, вы не хотите иметь результат отсортированным, вы можете указать ORDER BY NULL: INSERT INTO foo SELECT a,COUNT(*) FROM bar GROUP BY a ORDER BY NULL;

Как я уже говорил выше, этот ман я изучал, и их советы использовал, но ...

Что говорит explain select?

Цитата(Akina @ 6.5.2005, 14:56)

Что говорит explain select?

Как я уже говорил выше:

EXPLAIN SELECT psname,category,city FROM base WHERE category='1' ORDER BY date DESC LIMIT 0, 20

выдает:

table type possible_keys key key_len ref rows Extra
base ref category category 4 const 8998 Using where; Using filesort

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

Я не хочу показаться невежливым, но как я уже говорил выше,

поле date уже проиндесксировано: KEY `date` (`date`)

Я тоже не хочу показаться невежливым, но

Цитата(witali @ 6.5.2005, 22:40)

table type possible_keys key key_len ref rows Extra base ref category category 4 const 8998 Using where; Using filesort

Попробуй

Код

SELECT psname,category,city FROM base USE KEY (date) IGNORE KEY (category) WHERE category='1' ORDER BY date DESC LIMIT 0, 20

и то же без IGNORE KEY - что получится по времени?

Цитата(Akina @ 12.5.2005, 13:06)

Я тоже не хочу показаться невежливым, но Цитата(witali @ 6.5.2005, 22:40) table type possible_keys key key_len ref rows Extra base ref category category 4 const 8998 Using where; Using filesort Попробуй Код SELECT psname,category,city FROM base USE KEY (date) IGNORE KEY (category) WHERE category='1' ORDER BY date DESC LIMIT 0, 20 и то же без IGNORE KEY - что получится по времени?

Код

SELECT psname,category,city FROM base USE KEY (date) IGNORE KEY (category) WHERE category='1' ORDER BY date DESC LIMIT 0, 20

выдает: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE KEY (category) WHERE category='1' ORDER BY date DESC LIMIT 0, 20' at line 1


Но я объединил индексы:

ADD INDEX ( category,date )

и стало работать в 4 раза быстрее. Спасибо, Akina.

Э-э-э... а эта... версия-то мускула какая? мож и взаправду синтаксис посмотреть... попробуй вместо USE/IGNORE KEY использовать USE/IGNORE INDEX например... или просто сменить порядок объединения индексов...