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

Proger · 16.08.2012 17:52:42

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

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)

rgbeast · 17.08.2012 07:22:49

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

Proger · 17.08.2012 10:45:56

Это phpmyadmin

rgbeast · 17.08.2012 10:58:09

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`).

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

Proger · 17.08.2012 11:16:27

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

Proger · 17.08.2012 11:18:38

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

rgbeast · 17.08.2012 11:29:27

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 ...;

Proger · 17.08.2012 12:31:47

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

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

#1 16.08.2012 17:52:42

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

#2 17.08.2012 07:22:49

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

#3 17.08.2012 10:45:56

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

#4 17.08.2012 10:58:09

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

#5 17.08.2012 11:16:27

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

#6 17.08.2012 11:18:38

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

#7 17.08.2012 11:29:27

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

Proger написал:

#8 17.08.2012 12:31:47

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

Board footer