Форум MySQL

Здравствуйте, столкнулся с проблемой, запрос к базе отрабатывает до 8 секунд, что естественно не приемлемо. Запрос и структура базы ниже. В базе около миллиона записей.

SELECT * FROM `test_table1`  WHERE `field1` > 0 ORDER BY `field2` DESC LIMIT 90000, 1 CREATE TABLE `test_table1` (   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,   `field1` smallint(5) unsigned NOT NULL,   `field2` int(10) unsigned NOT NULL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=692564 DEFAULT CHARSET=utf8;

да, это из-за LIMIT. попробуйте для начала добавить индексы для полей field1 и field2

Забыл добавить индексы стоят там.
Да, лимит уменьшить нет возможности.
Пробывал трюк с join, немного помогает,
myisam тоже ускорить должен, но хотелось узнать, что именно в данном случае можно сделать.
Потому что итоговые ускорения не настоль велики.

скорее всего нужно оптимизировать запрос. можно на словах, что должен сделать запрос?

Как пример таблица с книгами,
в одной колонке размер картинки 0 - нет картинки
во второй есть рейтинг книги,
Надо отсортировать по рейтингу и показать только с картинками.
На локальной пробывал вынести наличие/отсутствие в отдельную колонку со значением (0/1)
Вроде ускорений особо не дало.
Тут больше проблема в сортировке и лимиту.

скорее всего LIMIT используете не так как надо. но если например нужно выбрать 50, то попробуйте так:

SELECT * FROM `books` WHERE `has_image` = 1 ORDER BY `rating` DESC LIMIT 50

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

Смотрите, сам запрос отрабатывает хорошо, если мы в начале списка

SELECT * FROM `books`  WHERE `has_image` = 1  ORDER BY `rating` DESC  LIMIT 100, 10

а если мы хотим выбрать ближе к концу например

SELECT * FROM `books`  WHERE `has_image` = 1  ORDER BY `rating` DESC  LIMIT 600100, 10

то запрос начинает долго отрабатываться.

попробуйте добавить еще один, составной индекс KEY(has_image, rating), отдельные индексы для этих полей можно пока убрать

не пойму вашей логики но явный признак не правильной архитектуры it это все что связано с информационными технологиями и нужно заранее понимать что где и как хранить инфу если LIMIT 600100, 10 600100 - это какой -то индефикатор ,- то эта беда а так не один нормальный узер не дойдет до этого ----- архив это очень много информации но которую редко используют или вообще не трогают
----------- значит информацию надо делить хотябы на архив и актуальные данные которых как правило значительно меньше архива значит будет очень быстрая выборка индексы делают очень похожий алгоритм почему то мало кто понимает простую истину!!!

artem01, как-то уж слишком вы безапелляционно. Может данные всей базы актуальны и нет возможности что-то выделить в архив.
>600100 - это какой -то индефикатор
это порядковый номер строки

SELECT * FROM `books`  WHERE `has_image` = 1  ORDER BY `rating` DESC  LIMIT 600100, 10

вы что не видите запрос --- вытащи все о книге где значения картинки = 1 отсортируй рейтинг по убыванию 10 строк начиная с 600100 строки это же полный бред !!!!

стандартный алгоритм хранения = SELECT * FROM `books` WHERE `id_books` = 1000 ORDER BY `id_books` DESC LIMIT 10

лол што? это то что вы написали полный бред. не о книге, а о книгах. когда-нибудь постраничную навигацию делали?

Как пример таблица с книгами,
в одной колонке размер картинки 0 - нет картинки
во второй есть рейтинг книги,
Надо отсортировать по рейтингу и показать только с картинками.
На локальной пробывал вынести наличие/отсутствие в отдельную колонку со значением (0/1)
Вроде ускорений особо не дало.
Тут больше проблема в сортировке и лимиту


по вашему описанию - ----

допустим в вашем книжном магазине есть 1000000 книг - значит у них должны быть свои полки !
допустим в магазине 100 полок ,то на каждую полку приходится по 10000 книг у каждои полки есть разделы например от а-я !
а у каждой книги есть свой автор или порядковый номер а еще есть дата печати или дата поступления в магазин а также элюстрация или
без элюстрации в вашем случае это с картинкой или без картинки и естественно вы в накладной вы можете отследить сколько раз купили эту книгу в вашем случае это рейтинг !

а если у вас не один магазин ? - тогда у магазина должен быть номер или название

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

например мне нужны книги из магазина №1 каторае лежат на 3 полке в разделе от а до б и с элюстрациями которые были куплины не менее 2 и не более 5 раэ за последнии 3 дня !
]
>лол што? это то что вы написали полный бред. не о книге, а о книгах. когда-нибудь постраничную навигацию делали?

на счет построничной навигации не один нормальный пользыватель не дойдет до 999999 страницы максимум до 10 если очень - очень надо то 15-20!!!

поэтому очень надо хорошо продумывать модель хранения данных и еще если у вас mysql то она должна кешировать ваши запросы
если она не кеширует то посмотрите в настройках query_cache_limit

или если у ва php то можно кешировать например так

$m = new memcached(); $key = 'запрос к базе данных'; if($m->get($key)  ){   print    $m->get($key);   // обрабатываем данные   } else{  $key = 'запрос к базе данных'; $value = 'ответ базы данных';  $m->set($key,$value,time()+300); // кешируем на 5 минут   }

это всё конечно хорошо, но мы рассматривали несколько синтетический пример и пытались оптимизировать время выполнения запроса. а все возможные варианты, о том как это можно решать, в том числе и про количество страниц которые пользователь будет просматривать, я привёл в сообщении ниже (ссылка на highload.com.ua)

вы бы сначала школу закончили, кудаж вы в лаптях в аспирантуру :)
>на счет построничной навигации не один нормальный пользыватель не дойдет до 999999 страницы максимум до 10 если очень - очень надо то 15-20!!!
вас как программиста этот фактор не должен ни коим образом е..ть, хочу и сижу листаю, ваша задача предоставить мне такую возможность пролистать от последней до первой книги сколько бы их там ни было, хоть 5 миллиардов

e..ть потом будут вам мозги когда сервер ляжет!!!

это с какого перепугу он должен лечь? хотя если на 3-ем пеньке, да с 128 оперативы, то да, ляжет
но это уже не мои проблемы, с моей стороны должен быть оптимизированный код, который работает по ТЗ
а вот если заказчик спросит у вас, почему я не могу перейти на 20-тую страницу, а вы ему ответите, что 19-ти страниц хватит за глаза (да и то это с запасом), никто дальше 20-то не пойдет...
вам скорее всего ничего не оветят, и не заплатят, и с работы уволят к чебурашке....

судя по этой фразе вы не знакомы с высоконагруженными сайтами

это с какого перепугу он должен лечь? хотя если на 3-ем пеньке, да с 128 оперативы, то да, ляжет

судя по вашим фразам, вы очень далеки от программирования в целом :)

я вы стесняюсь спросить в какой области спец? случайно не в php:)

не, я тут так потролить 6 лет сижу ;)

вы знаете я заметил что среди сообщества php очень часто попадаются хвастуны "стартапперы" а еще многие любят похаить друг друга вместо того что помочь , а еще очень много криво руких не потому что программировать в целом не умеют , а потому что не хотят нормально думать поэтому из многих проектов передоваемых от пхпешника к пехапешнику переписываются заново !!!(((

:)

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

WHERE `id` BETWEEN 600100 AND 600110

если такое нельзя проделать с id, можно ввести дополнительное поле отдельно для этих целей. будут конечно свои нюансы, если записи будут удаляться например, за этим придется следить.
тут еще надо понимать, что этот пример достаточно синтетический, в реальных условиях записи скорее всего выбирались бы с множеством дополнительных условий и было бы ограничение по категории и еще по каким-то другим параметрам, так что записей было бы на самом деле скорее всего на несколько порядков меньше и всё это работало бы быстрее. в общем желательно до конструкции LIMIT по возможности максимально ограничивать выборку с помощью WHERE, естественно чтобы это соответствовало поставленной задаче.
вот хорошая статья на этот счет http://highload.com.ua/index.php/2009/05/24/%D0%BE%D0%BF%D1%82%D0%B8%D0%BC%D0%B8%D0%B7%D0%B0%D1%86%D0%B8%D1%8F-%D0%BF%D0%BE%D1%81%D1%82%D1%80%D0%B0%D0%BD%D0%B8%D1%87%D0%BD%D0%BE%D0%B3%D0%BE-%D0%B2%D1%8B%D0%B2%D0%BE%D0%B4%D0%B0-%D0%B2-mysql/

индекс здесь если и поможет, то только составной - по полям (field1, field2) - и именно в таком порядке.
И таки да. выборка по номеру попадания в сортированной последовательности - явно нелучшее решение в смысле модели.