10 млн записей MySQL. Как сделать постраничный вывод в порядке убывания?

Есть таблица в MySQL в ней более 10 миллонов записей. Все это располагается на слабеньком сервере.
нужно сделать постраничный вывод по 50 записей. Количество записей увеличивается, но в общем количество запросов INSERT мало (50-100 в сутки), что это на производительность не влияет. Сам сайт и постраничная навигация реализуется на PHP.

Мне не нужно углубляться в самые старые записи по пагинации, хватит и 200 страниц пагинации по 50 записей каждая.
В принципе быстро отрабатывает "SELECT * FROM 1_vopros ORDER BY id DESC LIMIT $num OFFSET $start" - для первых 200 страниц (менее секунды) - что мне и нужно, но если добавить WHERE st=1 уже несколько секунд. (У st 3 значения либо 0 либо 1 либо 2). А вот WHERE cat='3' (Работает по скорости в зависимости от того сколько записей с этим значением - чем меньше тем быстрее - cat это ID категории).

как оптимизировать запрос с добавлением условий WHERE st='1' AND cat='ID категории' в порядке убывания по полю ID ? Индекс по этим полям есть. Пробывал и двойные индексы делать не помогло.

CREATE TABLE IF NOT EXISTS `1_vopros` (
  `id` int(12) NOT NULL,
  `vopr` text NOT NULL,
  `title` varchar(255) NOT NULL,
  `url` varchar(255) NOT NULL,
  `autor` varchar(255) NOT NULL,
  `autorid` int(12) NOT NULL,
  `date` datetime NOT NULL,
  `cat` tinyint(2) NOT NULL,
  `kls` tinyint(2) NOT NULL,
  `srcid` varchar(255) NOT NULL,
  `st` tinyint(1) NOT NULL,
  `src` tinyint(1) NOT NULL,
  `imgs` text NOT NULL,
  `view` int(50) NOT NULL
) ENGINE=MyISAM AUTO_INCREMENT=12603144 DEFAULT CHARSET=utf8;

ALTER TABLE `1_vopros`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `srcid` (`srcid`),
  ADD KEY `st` (`st`),
  ADD KEY `url` (`url`),
  ADD KEY `cat` (`cat`),
  ADD KEY `kls` (`kls`),
  ADD KEY `autor` (`autor`),
  ADD KEY `date` (`date`),
  ADD FULLTEXT KEY `vopr` (`vopr`,`title`);

--
-- AUTO_INCREMENT для сохранённых таблиц
--

--
-- AUTO_INCREMENT для таблицы `1_vopros`
--
ALTER TABLE `1_vopros`
  MODIFY `id` int(12) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=12603144;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
  • Вопрос задан
  • 1190 просмотров
Решения вопроса 1
BojackHorseman
@BojackHorseman Куратор тега PHP
...в творческом отпуске...
прежде всего, уберите сравнение целочисленных полей со строками, что влечет за собой приведение типов и подавление индексов.

добавьте индекс (cat, st, id desc) и покажите результат запроса
EXPLAIN SELECT id FROM 1_vopros where cat=3 AND st = 1 ORDER BY id DESC LIMIT 1000 OFFSET 1000


update. выборка в первом запросе только поля первичного ключа требует в этом случае только чтения одного индекса и не требует фетча записей с диска, что выгодно. далее вторым запросом по полученным значениям id выбираете требуемые записи простым быстрым запросом
SELECT * FROM 1_vopros WHERE id IN (<ids_list_separator_coma>);
Ответ написан
Пригласить эксперта
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы