Правила     Закладки     Карма    Календарь    Журналы    Помощь    Поиск    PDA    Чат   
        СМС-ки
   
Пейджер выключен!
Страницы: (3) [1] 2 3  ( Перейти к первому непрочитанному сообщению )  
Фильтр авторов:    показать 
  скрыть
  Ответ в темуСоздание новой темыСоздание опроса

> MySql составной индекс для запросов вида IN
Serg86  
 ۩  Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Старик
***

Профиль
Группа: Пользователь
Сообщений: 104
Пользователь №: 42253
На форуме: 1 год, 1 месяц, 5 дней
Карма:




Всем доброго времени суток. Пытался решить проблему самостоятельно, но во всех источниках моя проблема упоминается вскользь, решил спросить у знатоков.
Версия MySQL: 5.6.26

Есть таблица с кучей полей (не принципиально), больше миллиона строк. Очень часто выполняются запросы поиска вида:

SELECT ........ WHERE cat in (630,359,611,534,541,535,538,531... бывает до 100 штук) AND region IN (126,127,6652.... бывает тож до 100 штук) ORDER by raised desc, date_add desc LIMIT 0, 30

Сразу добавлю что переформулировать запрос не получается, так как перечисляются дочерние категории и дочерние регионы региона и категории заданных в запросе.

Пытался прикрутить индексы, но как показал explain, в случае применения конструкции IN составной индекс не срабатывает, берется только первое значение, cat или region, в зависимости от того как составлен индекс.
Получается чтобы выбрать 30 строк приходится опрашивать как минимум несколько тысяч лишних, соответственно и запрос выполняется 10 - 15 сек.

Остюда вопрос: Предусмотрено ли в механизме использования составных индексов конструкция IN, и если да то как попробывать поэкспериментировать. Если нет, подскажите возможные решения проблемы.
PMПисьмо на e-mail пользователю
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
sergeiss  
Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Сидел он, дум великих полон - и вдаль глядел
******

Профиль
Группа: Эксперт
Группа переписки
Сообщений: 15111
Пользователь №: 4190
На форуме: 8 лет, 11 месяцев, 13 дней
Карма: 448




Цитата (Serg86 @ 14.12.2015 - 18:09)
Пытался прикрутить индексы, но как показал explain, в случае применения конструкции IN составной индекс не срабатывает...

Как именно был составлен этот индекс?

Цитата (Serg86 @ 14.12.2015 - 18:09)
Есть таблица с кучей полей (не принципиально), больше миллиона строк.
...
Сразу добавлю что переформулировать запрос не получается, так как перечисляются дочерние категории и дочерние регионы региона и категории заданных в запросе.

По-моему, тут очень даже просятся партиции. Надо только правильно сделать дробление, чтобы оно работало максимально эффективно.

Цитата (Serg86 @ 14.12.2015 - 18:09)
Если нет, подскажите возможные решения проблемы.

Кроме партиций, возможно, тут получится сделать джойны? Для этого надо знать структуру таблицы.
Или, может быть, вообще как-то перестроить данные, разделить их на несколько таблиц? В любом случае показывай структуру своих данных, подумаем.


--------------------
* Хэлп по PHP
* Описалово по JavaScript
* Хэлп и СУБД для PostgreSQL

* Обучаю PHP, JS, вёрстке. Интерактивно и качественно. За разумные деньги.

* "накапливаю умение телепатии" (С) и "гуглю за ваш счет" (С)

user posted image
PMICQ
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
Serg86  
 ۩  Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Старик
***

Профиль
Группа: Пользователь
Сообщений: 104
Пользователь №: 42253
На форуме: 1 год, 1 месяц, 5 дней
Карма:




Цитата
Как именно был составлен этот индекс?

Пробывал простой составной индекс с типом index из 2 полей cat region.
Цитата
По-моему, тут очень даже просятся партиции. Надо только правильно сделать дробление, чтобы оно работало максимально эффективно.

Как мне казалось в партициях нельзя чтобы записи одной партиции были разбросаны по талице.
Цитата

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

Можно пподробней.
PMПисьмо на e-mail пользователю
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
sergeiss  
Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Сидел он, дум великих полон - и вдаль глядел
******

Профиль
Группа: Эксперт
Группа переписки
Сообщений: 15111
Пользователь №: 4190
На форуме: 8 лет, 11 месяцев, 13 дней
Карма: 448




Цитата (Serg86 @ 14.12.2015 - 19:45)
Можно поподробней.

Ну так покажи структуру своих данных smile.gif Тогда можно будет подробнее что-то сказать.


--------------------
* Хэлп по PHP
* Описалово по JavaScript
* Хэлп и СУБД для PostgreSQL

* Обучаю PHP, JS, вёрстке. Интерактивно и качественно. За разумные деньги.

* "накапливаю умение телепатии" (С) и "гуглю за ваш счет" (С)

user posted image
PMICQ
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
Serg86  
 ۩  Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Старик
***

Профиль
Группа: Пользователь
Сообщений: 104
Пользователь №: 42253
На форуме: 1 год, 1 месяц, 5 дней
Карма:




-- phpMyAdmin SQL Dump
-- version 4.5.2
-- http://www.phpmyadmin.net
--
-- Хост: localhost
-- Время создания: Дек 15 2015 г., 09:44
-- Версия сервера: 5.6.26-74.0
-- Версия PHP: 5.6.15

SET SQL_MODE =
"NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "
+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- База данных: `sait`
--

-- --------------------------------------------------------

--
-- Структура таблицы `sait_db`
--
-- Создание: Дек 14 2015 г., 12:19
--

DROP TABLE IF EXISTS `sait_db`;
CREATE TABLE `sait_db` (
`id` int(11) NOT NULL,
`p` int(11) NOT NULL,
`ip` varchar(15) NOT NULL,
`uid` varchar(100) NOT NULL,
`cat` int(5) DEFAULT NULL,
`region` int(5) DEFAULT NULL,
`date_add` int(11) DEFAULT NULL,
`date_moder` int(11) NOT NULL,
`date_edit` int(11) DEFAULT NULL,
`date_del` int(11) DEFAULT NULL,
`user_id` int(11) DEFAULT NULL,
`status` int(1) DEFAULT '1',
`sendmail` int(1) NOT NULL,
`sort` int(10) DEFAULT '10000000',
`email` varchar(100) DEFAULT NULL,
`user` varchar(200) NOT NULL,
`title` varchar(300) DEFAULT NULL,
`text` text,
`marked` int(1) NOT NULL DEFAULT '0',
`raised` int(1) NOT NULL DEFAULT '0',
`block` int(1) NOT NULL DEFAULT '0',
`active` int(1) NOT NULL DEFAULT '0',
`counter` int(11) NOT NULL,
`moder_id` int(11) DEFAULT NULL,
`moder_edit` int(11) NOT NULL,
`moder_vozvrat_id` int(11) DEFAULT NULL,
`kol_vozv` int(11) DEFAULT NULL,
`prizn_vozv` text NOT NULL,
`rpubl` int(11) NOT NULL,
`nosend` int(10) UNSIGNED NOT NULL DEFAULT '0',
`subdomain` char(255) DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


--
-- Индексы сохранённых таблиц
--

--
-- Индексы таблицы `sait_db`
--
ALTER TABLE `sait_db`
ADD PRIMARY KEY (`id`),
ADD KEY `region` (`region`),
ADD KEY `cat` (`cat`),
ADD KEY `cat_region` (`cat`,`region`),
ADD KEY `status` (`status`),
ADD KEY `block_status` (`block`,`status`),
ADD KEY `userid_status` (`user_id`,`status`),
ADD KEY `p` (`p`);

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

--
-- AUTO_INCREMENT для таблицы `sait_db`
--
ALTER TABLE `sait_db`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=243322;
/*!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 */;

PMПисьмо на e-mail пользователю
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
sergeiss  
Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Сидел он, дум великих полон - и вдаль глядел
******

Профиль
Группа: Эксперт
Группа переписки
Сообщений: 15111
Пользователь №: 4190
На форуме: 8 лет, 11 месяцев, 13 дней
Карма: 448




Serg86, попробуй использовать в запросе FORCE INDEX(cat_region).


--------------------
* Хэлп по PHP
* Описалово по JavaScript
* Хэлп и СУБД для PostgreSQL

* Обучаю PHP, JS, вёрстке. Интерактивно и качественно. За разумные деньги.

* "накапливаю умение телепатии" (С) и "гуглю за ваш счет" (С)

user posted image
PMICQ
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
Serg86  
 ۩  Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Старик
***

Профиль
Группа: Пользователь
Сообщений: 104
Пользователь №: 42253
На форуме: 1 год, 1 месяц, 5 дней
Карма:




Цитата
Serg86, попробуй использовать в запросе FORCE INDEX(cat_region).

Не прокатывает, только cat задействован.
PMПисьмо на e-mail пользователю
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
Serg86  
 ۩  Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Старик
***

Профиль
Группа: Пользователь
Сообщений: 104
Пользователь №: 42253
На форуме: 1 год, 1 месяц, 5 дней
Карма:




Я еще подумывал както subdomain задействовать, чтоб он не по всей таблице cat перебирал, но здесь похожая проблема, субдоменов может быть нескольки и их опять в in писать придется.
PMПисьмо на e-mail пользователю
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
S.Chushkin  
Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Пофигист
******

Профиль
Группа: Форумчанин
Завсегдатай форума
Сообщений: 784
Пользователь №: 36058
На форуме: 4 года, 22 дня
Карма: 40




Цитата (Serg86 @ 14.12.2015 - 18:09)
Получается чтобы выбрать 30 строк приходится опрашивать как минимум несколько тысяч лишних, соответственно и запрос выполняется 10 - 15 сек.

Запрос не простой, поэтому "вполне может быть".

Выложите куда-нибудь дамп и пришлите мне ссылку на него (в личку). И сам запрос, полный, который Вам нужен.
Вечером сделаю оптимальный вариант запроса для mySQL. Более оптимальный вряд-ли у Вас получится без переделки структуры БД.


--------------------
PM
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
Serg86  
 ۩  Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Старик
***

Профиль
Группа: Пользователь
Сообщений: 104
Пользователь №: 42253
На форуме: 1 год, 1 месяц, 5 дней
Карма:




Цитата
Запрос не простой, поэтому "вполне может быть".

Выложите куда-нибудь дамп и пришлите мне ссылку на него (в личку). И сам запрос, полный, который Вам нужен.
Вечером сделаю оптимальный вариант запроса для mySQL. Более оптимальный вряд-ли у Вас получится без переделки структуры БД.

Скинул в личку.
PMПисьмо на e-mail пользователю
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
sergeiss  
Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Сидел он, дум великих полон - и вдаль глядел
******

Профиль
Группа: Эксперт
Группа переписки
Сообщений: 15111
Пользователь №: 4190
На форуме: 8 лет, 11 месяцев, 13 дней
Карма: 448




Потом в теме напишите, как решили проблему. В том числе и сам запрос можно выложить.


--------------------
* Хэлп по PHP
* Описалово по JavaScript
* Хэлп и СУБД для PostgreSQL

* Обучаю PHP, JS, вёрстке. Интерактивно и качественно. За разумные деньги.

* "накапливаю умение телепатии" (С) и "гуглю за ваш счет" (С)

user posted image
PMICQ
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
S.Chushkin  
Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Пофигист
******

Профиль
Группа: Форумчанин
Завсегдатай форума
Сообщений: 784
Пользователь №: 36058
На форуме: 4 года, 22 дня
Карма: 40




ТС, Вы грозились:
1) больше миллиона строк
в дампе всего 50К
2) запрос выполняется 10 - 15 сек
у меня выполняется за 0.15с, из них 0.1 - ORDER BY

Уточните поточнее, что Вы хотите получить?



--------------------
PM
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
Serg86  
 ۩  Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Старик
***

Профиль
Группа: Пользователь
Сообщений: 104
Пользователь №: 42253
На форуме: 1 год, 1 месяц, 5 дней
Карма:




Цитата
больше миллиона строк
в дампе всего 50К

Да я всю таблицу не стал сливать просто, если надо солью больше
Цитата
у меня выполняется за 0.15с, из них 0.1 - ORDER BY

Будет миллион, будет больше, это логично.
Цитата
Уточните поточнее, что Вы хотите получить?

Хотел по средствам индексов задействовать как можно меньше строк и соответственно уменьшить время запроса. Составныеиндексы не работают почемуто, судя по explain задействуется только cat. Вчера похимичил посидел, удалось задействовать status_cat, время значительно снизилось.

Задача на выходе получить 30 строк из таблицы, при этом максимально снизить количество затронутых строк.
Цитата
из них 0.1 - ORDER BY

Подскажите, можно с этим както бороться?
PMПисьмо на e-mail пользователю
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
Serg86  
 ۩  Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Старик
***

Профиль
Группа: Пользователь
Сообщений: 104
Пользователь №: 42253
На форуме: 1 год, 1 месяц, 5 дней
Карма:




Еще заметил странную штуку, прикрутил новый индекс status_cat, погонял его немного, вроди время снизилось до адекватного, и я счастливый, на этом решил отложить проблему в долгий ящик. Через какоето время запускаю запрос снова и он выполняется за 15 секунд, тут же повторяю запрс и получаю 0,1 секунду, в чем прикол не пойму.
PMПисьмо на e-mail пользователю
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
Serg86  
 ۩  Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Старик
***

Профиль
Группа: Пользователь
Сообщений: 104
Пользователь №: 42253
На форуме: 1 год, 1 месяц, 5 дней
Карма:




Цитата
Уточните поточнее, что Вы хотите получить?

Там если вы обратиливнимание есть еще субдомены, каждому субдомену в таблице регионов прикреплен ряд регионов, те что в запросе перечислены, вот я и думал, что может область поиска сразу по субдомену сузть, к примеру:
where subdomain = 'moskva' and status = 1 and (всё остальное) вот только тогда есть несколько прблем:
1) Не всегда область поиска целиком является субдоменом, а иногда идет пересечене, к примеру:
Ставропольский край (субдомен), минераловодский район (не субдмен, у него куча дочерних нас пунктов), Минеральные Воды (субдомен). Если искать в минераловодском райое, то придется учесть все его дочерние нас пункты плюс субдомен.
2) Иногда искать по нескольким субдоменам, если взять логику первого пункта по дереву регионов и поискать по ставропольскому краю, тогда ищем в двух субдоменах, снова получаем конструкцию IN и не факт что составные индексы будут работать.
PMПисьмо на e-mail пользователю
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
  Быстрый ответ
Информация о Госте
Введите Ваше имя
Кнопки кодов
Для вставки цитаты, выделите нужный текст и
НАЖМИТЕ СЮДА
Введите сообщение
Смайлики
:huh:  :o  ;) 
:P  :D  :lol: 
B)  :rolleyes:  <_< 
:)  :angry:  :( 
:unsure:  :blink:  :ph34r: 
     
Показать всё

Опции сообщения  Включить смайлики?
 Включить подпись?
 
1 Пользователей читают эту тему (1 Гостей и 0 Скрытых Пользователей)
0 Пользователей:

Опции темыСтраницы: (3) [1] 2 3  Ответ в темуСоздание новой темыСоздание опроса