miksoftsgalaбудет за 0.22 с файлсортом, т.к. индекс для ордеринга включается только если (фанфары) выбираются записи из первой трети.
а поскольку там 60к записей с лишним, то 40к заплевывает в оставшиеся 70% и пролетайло получается
дляа можно ссылочку на такое?В старом мануале (по 5.4.3 это было прямо в разделе про индексы, но я уже потерял ту ссылку) :)
В свежем мануале http://dev.mysql.com/doc/refman/5.7/en/where-optimizations.html говорится что строгого правила про 30% уже нет,
At one time, a scan was used based on whether the best index spanned more than 30% of the table, but a fixed percentage no longer determines the choice between using an index or a scan. The optimizer now is more complex and bases its estimate on additional factors such as table size, number of rows, and I/O block size.
но по факту у меня на практике получилось как раз 30% граница

miksoftА когда выбирается 2/3 записей логично, что индекс не поможет.Побуду занудой. Как раз помог. С индексом (форсе индекс) выбирается за 0.05, без индекса за 0.22.
Вот теперь мне бы этот индекс к "большому" запросу прикрутить как-то.

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



категорически не понимаю:( что бы именно для ордера и все такое.
Есть какие-то идеи? Тут получается, что отбор идет по одной таблице, а сортировка по другой. Имхо, хорошего варианта не получится. Поэтому и приходится придумывать обходные варианты. Например, все тот же перенос поля для сортировки в таблицу categories_rel.
mysql-ный мануал говорит что должно работать если колонка "ордер бай" из первой константной таблицы, а у меня запрос именно такой. Т.е. первая таблица как раз с ордербаем и константная дальше ехать некуда.
http://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html
In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause. These cases include the following:
The query joins many tables, and the columns in the ORDER BY are not all from the first nonconstant table that is used to retrieve rows.

miksoftsgalaТ.е. первая таблица как раз с ордербаем и константная дальше ехать некуда.Константная-то как раз вторая, судя по where b .catid=982734да, тупанул. спасибо.
Буду думать что с этим делать.

[quot MasterZiv]sgala.
ты не умеешь писать запросы.
left join categories_rel b on a.goodid=b.goodid where b.catid=982734
left join тут не нужен нигде, ты фильтруешь по этой таблице b.То что я делаю как-то неправильно это я понимаю, иначе не были бы такие результаты.
Не могли бы вы подсказать как в моей ситуации сделать правильно?
Если думаете предложить вариант с вложенным запросом - обратите внимание на то, что один из таких вариантов уже был приведен, обсужден и отвергнут.

miksoft , попробовал сделать таблицу чисто для сортировки "goodid, categoryid, orderid". В принципе стало получше, правда не знаю к чему это приведет когда добавлю еще полей.
Что бы стало совсем хорошо попробовал сделать из нее memory table и вот тут был удивлен (обе таблицы myisam, индексы btree, smallint, mediumint, mediumint).
Во-первых размеры. memory таблица с нужными ключами улетает за 1500мб, в то время как myisam занимает культурные 244мб.
Мало того что "размер строки" (без индексов) в memory 21 байт против 9 байт в myisam (откуда 21-то? на 3 поля по 4 байта = 12 должно быть вроде). Так еще и ключи занимают почти в 4 раза больше места.

Во-вторых разница в эксплеинах. Таблицы полностью одинаковые, на всякий случай сделал optimize, analyze и даже ребутнул сервер.
Запрос SELECT * FROM `goodsORD` WHERE catid=85798 order by orderid limit 40000,50
Но memory эксплеин выглядит как
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE goodsORD ref orderid orderid 2 const 733 Using where
А myisam-овский
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE goodsORD ref orderid orderid 2 const 64139 Using where
Т.е. вот тут я вообще не понял. Какого черта (при чем эксплеины запускал несколько раз, ситуация аналогичная, хотя цифры могут плавать) в myisam-овской таблице при всех прочих равных - ему нужно перебрать в 100 раз больше строк ?! Т.е. по сути все, а memory довольствуется 733 ?

MasterZivsgala,
я же написал, тебе не нужен left join.так что нужно-то?

tanglirsgala,

[inner] join

ну и в самомделе, зачем вам лимит 40000?с inner join та же самая ерунда. т.е. даже эксплеин один в один.

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

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

но запросы вида goodid in (50 значений) работают ОЧЕНЬ долго запускаясь первый раз, хотя казалось бы куда проще-то запрос.
вплоть до 5 секунд, хотя со второго раза стабильно укладываются в 0.05 секунды, а то и 0.005 секунды даже. если чутка подождать, то время увеличивается до 0.5 секунды.
при чем эксплеин показывает index: range. никакого файлсорта и прочей ерунды
отчего такая ерунда?

key_buffer_size выставил в 1024м - казалось бы индексы всяко должны быть в памяти (всего их 700мб) но не фига не помогает.

miksoftsgalakey_buffer_size выставил в 1024м - казалось бы индексы всяко должны быть в памяти (всего их 700мб) но не фига не помогает.Откуда им быть в памяти, если их до этого никто не читал?
Сам по себе кэш индексов не заполняется, а только по мере использования этих индексов.
э.
вы хотите сказать, что файл индексов (при достаточности памяти) попадет в память ПОЛНОСТЬЮ тогда и ТОЛЬКО тогда когда все индексы хоть раз прочитаны?
я почему-то считал что индексный файл грузится в память если не при старте мускула, то как минимум после первого обращения.

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