SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 16.08.2012 17:52:42

Proger
Активист
Откуда: Санкт-Петербург
Зарегистрирован: 04.09.2008
Сообщений: 172

Помогите оптимизировать сложный запрос

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

SELECT SQL_CALC_FOUND_ROWS ss.index, ss.site, fb.`name` , AVG( sp.`position` ) AS avg_position, COUNT( * ) AS queries, ss.clicks_total_strict, ss.clicks_total_phrase
FROM sitepos_sites ss
LEFT JOIN sitepos_positions sp ON ss.index = sp.site_index
LEFT JOIN firms_base fb ON fb.index = ss.clinic
WHERE sp.`date` =  '2012-08-16'
GROUP BY ss.site
ORDER BY ss.clicks_total_strict DESC


EXPLAIN:

почикано

Таблицы:


CREATE TABLE IF NOT EXISTS `sitepos_keywords` (
  `index` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `city` int(11) NOT NULL,
  `date` date NOT NULL,
  `keyword` varchar(700) NOT NULL,
  `adwords_phrase` int(11) NOT NULL,
  `adwords_strict` int(11) NOT NULL,
  PRIMARY KEY (`index`)
) ENGINE=MyISAM  DEFAULT CHARSET=cp1251 AUTO_INCREMENT=1933 ;

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

CREATE TABLE IF NOT EXISTS `sitepos_positions` (
  `index` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `city` int(11) NOT NULL,
  `keyword_index` int(11) NOT NULL,
  `site_index` int(11) NOT NULL,
  `position` int(11) NOT NULL,
  `clicks_phrase` int(11) NOT NULL,
  `clicks_strict` int(11) NOT NULL,
  PRIMARY KEY (`index`),
  KEY `site_index` (`site_index`),
  KEY `date` (`date`)
) ENGINE=MyISAM  DEFAULT CHARSET=cp1251 AUTO_INCREMENT=38661 ;

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

CREATE TABLE IF NOT EXISTS `sitepos_sites` (
  `index` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `city` int(11) NOT NULL,
  `site` text NOT NULL,
  `clinic` int(11) NOT NULL,
  `clicks_total_phrase` int(11) NOT NULL,
  `clicks_total_strict` int(11) NOT NULL,
  PRIMARY KEY (`index`)
) ENGINE=MyISAM  DEFAULT CHARSET=cp1251 AUTO_INCREMENT=6891 ;
 

Отредактированно Proger (17.08.2012 10:46:23)

Неактивен

 

#2 17.08.2012 07:22:49

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3879

Re: Помогите оптимизировать сложный запрос

Выполните EXPLAIN с \G на конце вместо точки с запятой, а то трудно разобраться в неформатированном

Неактивен

 

#3 17.08.2012 10:45:56

Proger
Активист
Откуда: Санкт-Петербург
Зарегистрирован: 04.09.2008
Сообщений: 172

Re: Помогите оптимизировать сложный запрос

Это phpmyadmin sad

http://s2.hostingkartinok.com/uploads/images/2012/08/587eaa78c764bd6fa4aaa7e92dbb39b8.jpg

Неактивен

 

#4 17.08.2012 10:58:09

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3879

Re: Помогите оптимизировать сложный запрос

1. не делайте JOIN с firms_base - это несколько разгрузит запрос. Названия фирм получите отдельным запросом по заданным id.
2. В идеале таблица sitepos_sites тоже не нужна - можно информацию из нее получить потом. Также можно потом выполнить сортировку уже имея статистические данные

Итак делаем запрос только к sitepos_positions

SELECT sp.site_index, AVG( sp.`position` ) AS avg_position, COUNT( * ) AS queries FROM sitepos_positions sp WHERE sp.`date` =  '2012-08-16' GROUP BY sp.site_index;
 


Вам нужно добавить составной индекс:
ALTER TABLE sitepos_positions ADD KEY(`date`, `site_index`);
 

При этом индекс на `date` не потребуется, так как составной сможет его заменить (но он не сможет заменить отдельный индекс на `site_index`).

Понимаю, что это не полностью решение, а направление в котором можно работать.

Неактивен

 

#5 17.08.2012 11:16:27

Proger
Активист
Откуда: Санкт-Петербург
Зарегистрирован: 04.09.2008
Сообщений: 172

Re: Помогите оптимизировать сложный запрос

Спасибо! В целом понял всё, кроме пункта про сортировку имея статистические данные.
Сортировка же по полю из sitespos_sites, как результаты одного запроса сортировать с помощь юдругого или тут же нужно "костылить" с помощью самого приложения? Массивы сортируя?

Неактивен

 

#6 17.08.2012 11:18:38

Proger
Активист
Откуда: Санкт-Петербург
Зарегистрирован: 04.09.2008
Сообщений: 172

Re: Помогите оптимизировать сложный запрос

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

Неактивен

 

#7 17.08.2012 11:29:27

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3879

Re: Помогите оптимизировать сложный запрос

Proger написал:

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

Да, все верно, тут возможны варианты. Один из наиболее прозрачных - использование временной таблицы для посчитанных данных, а потом JOIN с ней (и сортировка).

DROP TEMPORARY TABLE IF EXISTS x;
CREATE TEMPORARY TABLE x SELECT sp.site_index, AVG( sp.`position` ) AS avg_position, COUNT( * ) AS queries FROM sitepos_positions sp WHERE sp.`date` =  '2012-08-16' GROUP BY sp.site_index;
SELECT ... FROM x JOIN ... JOIN ...;
 

Неактивен

 

#8 17.08.2012 12:31:47

Proger
Активист
Откуда: Санкт-Петербург
Зарегистрирован: 04.09.2008
Сообщений: 172

Re: Помогите оптимизировать сложный запрос

Супер, получилось в 5 раз быстрее smile

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson