Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Оптимизация запроса (MySql vs PostgreSql) / 13 сообщений из 13, страница 1 из 1
10.02.2015, 18:27:56
    #38876290
undiabler
undiabler
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса (MySql vs PostgreSql)
Суть, есть вектор строковых значений, длинна вектора динамическая, строковые значения ограниченны длинной 255 символов. Для расчетов нужно получить численные представления этих строковых значений(грубо говоря построить все уникальные в ряд и пронумеровать). После этого нужно сделать выборку всех векторов по условию при этом заменив строковые данные - численными.

C переназначением проблем нет. А вот с выборкой проблема. Выборку делает вот такой сгенерированный запрос (на примере вектора из 4х параметров):


Код: plsql
1.
2.
3.
4.
5.
6.
SELECT rm0.map_id as o0 , rm1.map_id as o1 , rm2.map_id as o2 , rm3.map_id as o3 , t.total, t.good
FROM  ( SELECT o0 , o1 , o2 , o3 , count(*) as total, sum(converted) as good FROM collect WHERE group_id=2 GROUP BY o0 , o1 , o2 , o3  ) AS t
 INNER JOIN remap AS rm0 ON ( rm0.group_id = 2 AND rm0.o_n = 'o0' AND rm0.name = t.o0 )  
 INNER JOIN remap AS rm1 ON ( rm1.group_id = 2 AND rm1.o_n = 'o1' AND rm1.name = t.o1 )  
 INNER JOIN remap AS rm2 ON ( rm2.group_id = 2 AND rm2.o_n = 'o2' AND rm2.name = t.o2 )  
 INNER JOIN remap AS rm3 ON ( rm3.group_id = 2 AND rm3.o_n = 'o3' AND rm3.name = t.o3 )



данный запрос при 15 тысячах данных (1400 уникальных элементов в remap и 15тысяч всего данных в collect) вешает MySql намертво ( 20-25секунд запрос идет), PostreSql на той же машине выдает 615 мсек что приемлемо.

Вопросы:

1. Можно ли оптимизировать данный запрос под mysql?

2. Какие есть еще варианты решения подобной задачи?

3. Какие в этом случае стоит использовать индексы?

4. Можно ли в явном виде сделать join с пересечением по всем 4м параметрам?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.02.2015, 18:34:41
    #38876291
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса (MySql vs PostgreSql)
Покажите план запроса и DDL обоих таблиц с индексами.
Подзапрос за какое время выполняется?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.02.2015, 18:56:45
    #38876297
undiabler
undiabler
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса (MySql vs PostgreSql)
1 PRIMARY rm0 ref PRIMARY PRIMARY 54 const,const 4 Using where
1 PRIMARY rm1 ref PRIMARY PRIMARY 54 const,const 4 Using where
1 PRIMARY rm3 ref PRIMARY PRIMARY 54 const,const 8 Using where
1 PRIMARY rm2 ref PRIMARY PRIMARY 54 const,const 395 Using where
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 1498 Using where; Using join buffer
2 DERIVED collect ALL NULL NULL NULL NULL 16442 Using where; Using temporary; Using filesort

Подзапрос выгребает за 0.0011

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
CREATE TABLE IF NOT EXISTS `collect` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `group_id` int(11) NOT NULL,
  `weight1` decimal(7,5) NOT NULL,
  `weight2` decimal(7,5) NOT NULL,
  `o1` varchar(255) DEFAULT NULL,
  `o2` varchar(255) DEFAULT NULL,
  `o3` varchar(255) DEFAULT NULL,
  `o4` varchar(255) DEFAULT NULL,
  `o5` varchar(255) DEFAULT NULL,
  `o6` varchar(255) DEFAULT NULL,
  `o7` varchar(255) DEFAULT NULL,
  `o8` varchar(255) DEFAULT NULL,
  `o9` varchar(255) DEFAULT NULL,
  `o10` varchar(255) DEFAULT NULL,
  `created` bigint(20) NOT NULL,
  `updated` bigint(20) NOT NULL DEFAULT '0',
  `converted` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=16341 ;


вектор условно ограничен длинной 10(по колонкам oN), в дальнейшем может быть увеличен.

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
CREATE TABLE IF NOT EXISTS `remap` (
  `group_id` int(11) NOT NULL,
  `o_n` varchar(16) NOT NULL,
  `name` varchar(255) NOT NULL,
  `map_id` int(11) NOT NULL,
  `total` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`group_id`,`o_n`,`map_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;



Групп может быть сразу много, соотв ключи для ремапа делались из расчета того что в одной группе все o_n названия уникальные и соотв их числовые эквиваленты.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.02.2015, 21:06:04
    #38876360
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса (MySql vs PostgreSql)
При практически полном отсутствии индексов не удивительно, что запрос тормозит.
Для начала попробуйте remap (group_id, o_n, name)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.02.2015, 22:19:26
    #38876401
ScareCrow
ScareCrow
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса (MySql vs PostgreSql)
авторПодзапрос выгребает за 0.0011
подзапросы выполняются на каждую строку. надо переводить на джойн/времянку.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.02.2015, 23:22:12
    #38876427
undiabler
undiabler
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса (MySql vs PostgreSql)
Первая серьезная выявленная проблема, если поменять INNER JOIN на LEFT JOIN, а потом выбросить все где есть NULL то время запроса сокращается с 25 до 4 секунд.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.02.2015, 23:37:47
    #38876434
ScareCrow
ScareCrow
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса (MySql vs PostgreSql)
план покажи
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.02.2015, 00:53:58
    #38876460
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса (MySql vs PostgreSql)
ScareCrowавторПодзапрос выгребает за 0.0011
подзапросы выполняются на каждую строку. надо переводить на джойн/времянку.Там уже JOIN. Только он выполняется не в том направлении. Индексов практически нет, поэтому оптимизатору сложно угадать правильное направление.
undiablerПервая серьезная выявленная проблема, если поменять INNER JOIN на LEFT JOIN, а потом выбросить все где есть NULL то время запроса сокращается с 25 до 4 секунд.Это не "проблема". Это вы методом "молотком по пальцам" заставили оптимизатор выполнять JOIN в правильном направлении.
Но лучше создать нормальные индексы. Вариант я уже предлагал.
ScareCrowплан покажиВот же - 17246330
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.02.2015, 01:47:45
    #38876485
ScareCrow
ScareCrow
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса (MySql vs PostgreSql)
miksoft,
Код: sql
1.
PRIMARY KEY (`group_id`,`o_n`,`map_id`)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.02.2015, 02:07:37
    #38876488
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса (MySql vs PostgreSql)
ScareCrowmiksoft,
Код: sql
1.
PRIMARY KEY (`group_id`,`o_n`,`map_id`)

И толку от него?
Из всех полей этой таблицы, участвующих в запросе, насколько я понимаю, самое селективное поле это name, а оно в этот индекс не входит.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.02.2015, 17:12:21
    #38877263
undiabler
undiabler
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса (MySql vs PostgreSql)
Да, индексы помогли, стало по 1.2-1.8 секунды отрабатывать.
Метод "молотка по пальцам" в принципе уместен т.как логика работы не страдает.

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

Код: plsql
1.
2.
3.
4.
5.
...
 INNER JOIN remap0 AS rm0 ON ( rm0.group_id = 2  AND rm0.name = t.o0 )  
 INNER JOIN remap1 AS rm1 ON ( rm1.group_id = 2  AND rm1.name = t.o1 )  
 INNER JOIN remap2 AS rm2 ON ( rm2.group_id = 2  AND rm2.name = t.o2 )  
 INNER JOIN remap3 AS rm3 ON ( rm3.group_id = 2  AND rm3.name = t.o3 )
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.02.2015, 17:29:43
    #38877277
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса (MySql vs PostgreSql)
undiablerДа, индексы помогли, стало по 1.2-1.8 секунды отрабатывать.Покажите новый план. Возможно, можно еще что-то улучшить.
undiablerМетод "молотка по пальцам" в принципе уместен т.как логика работы не страдает.Все равно он менее эффективен, чем правильные индексы. Хотя иногда имеет право на жизнь в случае ограниченных ресурсов и/или при отсутствии требований по времени выполнения запроса.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.02.2015, 18:27:46
    #38877319
tanglir
tanglir
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса (MySql vs PostgreSql)
miksoft,

или когда данные такие, что анализатор хреновую статистику собирает, сколько его по ним ни прогоняй.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Оптимизация запроса (MySql vs PostgreSql) / 13 сообщений из 13, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 305ms
Закрыть
start [/forum/topic.php?fid=47&mobile=1&tid=1833585]:
 0ms
get settings:
 4ms
get forum list:
 9ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 47ms
get topic data:
 6ms
get forum data:
 1ms
get page messages:
 27ms
get tp. blocked users:
 1ms
others: 206ms
total:  305ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]