Меню
Что нового?

Оптимизация SQL запроса

SPavel_74100

Новичок
Оптимизация SQL запроса

PHP: 
select ug.*,
up.id as idprice,up.price,up.deteact as deteactasprice,up.datedoc as datedocprice
from `usergoods` ug
left outer join `userprice` up on up.idgoods=ug.id and up.deteact = (select deteact from `userprice` where `idgoods`=ug.id order by 1 DESC limit 0,1)
where ug.idgroup = '1'		
order by ug.fname,up.price;
где `usergoods` - товары, `userprice` - цены на товары

Цель sql: получить список товаров с последней ценой в группе(ug.idgroup) 1.

В таблице `userprice` хранится боле 10 цен на 1 товар. У вех цен есть Дата активации(up.deteact)

Чем больше товаров в группе, тем медленнее работает запрос. При 300 записях в таблице `usergoods`, группе 1 - время выполнения 5 сек.

Вопрос в том, как оптимизировать запрос ?

MySQL 5
 

Gas

может по одной?
давай create table обоих таблиц и explain запроса.
 

SPavel_74100

Новичок
PHP: 
CREATE TABLE `usergoods` (
  `Id` int(11) NOT NULL auto_increment,
  `idproject` int(11) default '0',
  `idtypeform` int(11) default '0',
  `idgroup` int(11) default NULL,
  `fname` text,
  `datedoc` datetime default NULL,
  `idfotomin` int(11) default NULL,
  `keyword` text,
  PRIMARY KEY  (`Id`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;


CREATE TABLE `userprice` (
  `Id` int(11) NOT NULL auto_increment,
  `idproject` int(11) default '0',
  `idtypeform` int(11) default '0',
  `idgoods` int(11) default NULL,
  `deteact` datetime default NULL,
  `price` double(10,2) default NULL,
  `datedoc` datetime default NULL,
  `idarchprices` int(11) default NULL,
  PRIMARY KEY  (`Id`)
) ENGINE=MyISAM  DEFAULT CHARSET=cp1251;
-~{}~ 19.02.09 00:34:

PHP: 
    [0] => Array
        (
            [id] => 1
            [select_type] => PRIMARY
            [table] => ug
            [type] => ALL
            [possible_keys] => 
            [key] => 
            [key_len] => 
            [ref] => 
            [rows] => 4089
            [Extra] => Using where; Using temporary; Using filesort
        )

    [1] => Array
        (
            [id] => 1
            [select_type] => PRIMARY
            [table] => up
            [type] => ALL
            [possible_keys] => 
            [key] => 
            [key_len] => 
            [ref] => 
            [rows] => 4416
            [Extra] => 
        )

    [2] => Array
        (
            [id] => 2
            [select_type] => DEPENDENT SUBQUERY
            [table] => userprice
            [type] => ALL
            [possible_keys] => 
            [key] => 
            [key_len] => 
            [ref] => 
            [rows] => 4416
            [Extra] => Using where; Using filesort
        )
 

SPavel_74100

Новичок
Спасибо. 4000 строк за 0.6 сек.

Скажите, пожалуйста, нужно ли пересоздавать индексы или переиндексировать таблицы при добавлении в них новых строк ?
 
Wicked

Wicked

Новичок
там correlated subquery, который дергается на каждую строку результата.

я бы предложил небольшую избыточность: ввести флажок актуальности в userprice. Тогда запрос будет выглядеть так:
PHP: 
select ug.*,
up.id as idprice,up.price,up.deteact as deteactasprice,up.datedoc as datedocprice
from `usergoods` ug
left outer join `userprice` up on up.idgoods=ug.id and up.is_actual = 1
where ug.idgroup = '1'        
order by ug.fname,up.price;
-~{}~ 19.02.09 18:00:

без введения этого поля можно попробовать поиграться с обходным путем проверки правила "последняя дата активации" следующим образом:
выбираем записи из userprice, для которых не существует записей в userprice с более поздней датой.
PHP: 
select ug.*,
up.id as idprice,up.price,up.deteact as deteactasprice,up.datedoc as datedocprice
from `usergoods` ug
left outer join `userprice` up on up.idgoods=ug.id
left outer join `userprice` up2 on up2.idgoods=up.idgoods and up.deteact < up2.deteact
where ug.idgroup = '1' and up2.deteact is null
order by ug.fname,up.price;
 

SPavel_74100

Новичок
Все записи 4000, без ug.idgroup и добавленно сумма количесва товара на всех складах
PHP: 
        select ug.*,
        up.id as idprice,up.price,up.deteact as deteactasprice,up.datedoc as datedocprice,
        sum(ua.amount) as goodsamount
        from `usergoods` ug
		left outer join `userprice` up on up.idgoods=ug.id and up.deteact = (select deteact from `userprice` where `idgoods`=ug.id order by 1 DESC limit 0,1)
		left outer join `useramount` ua on ug.id=ua.idgoods
		group by ug.id
		order by ug.fname,up.price; 


CREATE TABLE `usergoods` (
  `Id` int(11) NOT NULL auto_increment,
  `idproject` int(11) default '0',
  `idtypeform` int(11) default '0',
  `idgroup` int(11) default NULL,
  `fname` text,
  `datedoc` datetime default NULL,
  `idfotomin` int(11) default NULL,
  `keyword` text,
  PRIMARY KEY  (`Id`),
  KEY `IdGroup` (`idgroup`)
) ENGINE=MyISAM AUTO_INCREMENT=4090 DEFAULT CHARSET=cp1251;


CREATE TABLE `userprice` (
  `Id` int(11) NOT NULL auto_increment,
  `idproject` int(11) default '0',
  `idtypeform` int(11) default '0',
  `idgoods` int(11) default NULL,
  `deteact` datetime default NULL,
  `price` double(10,2) default NULL,
  `datedoc` datetime default NULL,
  `idarchprices` int(11) default NULL,
  PRIMARY KEY  (`Id`),
  KEY `InGoodsDateact` (`idgoods`,`deteact`)
) ENGINE=MyISAM AUTO_INCREMENT=4418 DEFAULT CHARSET=cp1251;


    [0] => Array
        (
            [0] => 1
            [id] => 1
            [1] => PRIMARY
            [select_type] => PRIMARY
            [2] => ug
            [table] => ug
            [3] => ALL
            [type] => ALL
            [4] => 
            [possible_keys] => 
            [5] => 
            [key] => 
            [6] => 
            [key_len] => 
            [7] => 
            [ref] => 
            [8] => 4252
            [rows] => 4252
            [9] => Using temporary; Using filesort
            [Extra] => Using temporary; Using filesort
        )

    [1] => Array
        (
            [0] => 1
            [id] => 1
            [1] => PRIMARY
            [select_type] => PRIMARY
            [2] => up
            [table] => up
            [3] => ref
            [type] => ref
            [4] => InGoodsDateact
            [possible_keys] => InGoodsDateact
            [5] => InGoodsDateact
            [key] => InGoodsDateact
            [6] => 14
            [key_len] => 14
            [7] => nullblokform.ug.Id,func
            [ref] => nullblokform.ug.Id,func
            [8] => 1
            [rows] => 1
            [9] => 
            [Extra] => 
        )

    [2] => Array
        (
            [0] => 1
            [id] => 1
            [1] => PRIMARY
            [select_type] => PRIMARY
            [2] => ua
            [table] => ua
            [3] => ALL
            [type] => ALL
            [4] => 
            [possible_keys] => 
            [5] => 
            [key] => 
            [6] => 
            [key_len] => 
            [7] => 
            [ref] => 
            [8] => 2
            [rows] => 2
            [9] => 
            [Extra] => 
        )

    [3] => Array
        (
            [0] => 2
            [id] => 2
            [1] => DEPENDENT SUBQUERY
            [select_type] => DEPENDENT SUBQUERY
            [2] => userprice
            [table] => userprice
            [3] => ref
            [type] => ref
            [4] => InGoodsDateact
            [possible_keys] => InGoodsDateact
            [5] => InGoodsDateact
            [key] => InGoodsDateact
            [6] => 5
            [key_len] => 5
            [7] => nullblokform.ug.Id
            [ref] => nullblokform.ug.Id
            [8] => 1
            [rows] => 1
            [9] => Using where; Using index; Using filesort
            [Extra] => Using where; Using index; Using filesort
        )
-~{}~ 19.02.09 15:37:

Автор оригинала: Wicked
там correlated subquery, который дергается на каждую строку результата.

я бы предложил небольшую избыточность: ввести флажок актуальности в userprice. Тогда запрос будет выглядеть так:
PHP: 
select ug.*,
up.id as idprice,up.price,up.deteact as deteactasprice,up.datedoc as datedocprice
from `usergoods` ug
left outer join `userprice` up on up.idgoods=ug.id and up.is_actual = 1
where ug.idgroup = '1'        
order by ug.fname,up.price;
Нажмите для раскрытия...
Да согласен быстрее но не корректно. Пользователь - хочешь добавить новую цену убери флаг со старой.
 

x-yuri

Новичок
SPavel_74100 никто пользователю это поручать не предлагал ;-)
 

Gas

может по одной?
там correlated subquery, который дергается на каждую строку результата.
Нажмите для раскрытия...
дёргается, но дёргается только одна запись из индекса, не известно ещё что будет быстрее.

SPavel_74100
замени "limit 0,1" просто на "limit 1", а то "Using filesort" у DEPENDENT SUBQUERY скорее всего из-за этого показывается.
Можно ещё на таблицу useramount навесить индекс (idgoods, ammount) - заметной прибавки в скорости на нынешних данных он может и не даст, но explain будет красивее :)
 

SPavel_74100

Новичок
Автор оригинала: Gas
дёргается, но дёргается только одна запись из индекса, не известно ещё что будет быстрее.

SPavel_74100
замени "limit 0,1" просто на "limit 1", а то "Using filesort" у DEPENDENT SUBQUERY скорее всего из-за этого показывается.
Можно ещё на таблицу useramount навесить индекс (idgoods, ammount) - заметной прибавки в скорости на нынешних данных он может и не даст, но explain будет красивее :)
Нажмите для раскрытия...
с limit 1
PHP: 
    [0] => Array
        (
            [0] => 1
            [id] => 1
            [1] => PRIMARY
            [select_type] => PRIMARY
            [2] => ug
            [table] => ug
            [3] => ALL
            [type] => ALL
            [4] => 
            [possible_keys] => 
            [5] => 
            [key] => 
            [6] => 
            [key_len] => 
            [7] => 
            [ref] => 
            [8] => 4284
            [rows] => 4284
            [9] => Using temporary; Using filesort
            [Extra] => Using temporary; Using filesort
        )

    [1] => Array
        (
            [0] => 1
            [id] => 1
            [1] => PRIMARY
            [select_type] => PRIMARY
            [2] => up
            [table] => up
            [3] => ref
            [type] => ref
            [4] => InGoodsDateact
            [possible_keys] => InGoodsDateact
            [5] => InGoodsDateact
            [key] => InGoodsDateact
            [6] => 14
            [key_len] => 14
            [7] => nullblokform.ug.Id,func
            [ref] => nullblokform.ug.Id,func
            [8] => 1
            [rows] => 1
            [9] => 
            [Extra] => 
        )

    [2] => Array
        (
            [0] => 1
            [id] => 1
            [1] => PRIMARY
            [select_type] => PRIMARY
            [2] => ua
            [table] => ua
            [3] => ALL
            [type] => ALL
            [4] => 
            [possible_keys] => 
            [5] => 
            [key] => 
            [6] => 
            [key_len] => 
            [7] => 
            [ref] => 
            [8] => 2
            [rows] => 2
            [9] => 
            [Extra] => 
        )

    [3] => Array
        (
            [0] => 2
            [id] => 2
            [1] => DEPENDENT SUBQUERY
            [select_type] => DEPENDENT SUBQUERY
            [2] => userprice
            [table] => userprice
            [3] => ref
            [type] => ref
            [4] => InGoodsDateact
            [possible_keys] => InGoodsDateact
            [5] => InGoodsDateact
            [key] => InGoodsDateact
            [6] => 5
            [key_len] => 5
            [7] => nullblokform.ug.Id
            [ref] => nullblokform.ug.Id
            [8] => 1
            [rows] => 1
            [9] => Using where; Using index; Using filesort
            [Extra] => Using where; Using index; Using filesort
        )
 
Wicked

Wicked

Новичок
дёргается, но дёргается только одна запись из индекса, не известно ещё что будет быстрее.
Нажмите для раскрытия...
я тоже не в курсе, но если добиться "not exists" в explain extras, мне такой план кажется вполне привлекательным

-~{}~ 19.02.09 19:05:

а что будет, если добавить индекс по паре (ug.idgroup, ug.fname) ?
 

SPavel_74100

Новичок
Автор оригинала: Wicked
я тоже не в курсе, но если добиться "not exists" в explain extras, мне такой план кажется вполне привлекательным

-~{}~ 19.02.09 19:05:

а что будет, если добавить индекс по паре (ug.idgroup, ug.fname) ?
Нажмите для раскрытия...
fname - type Text
 
LONGMAN

LONGMAN

Dark Side of the Moon..
Можно средствами php увидеть результат запроса EXPLAIN? И как это сделать
 
phprus

phprus

Moderator
Команда форума
LONGMAN
Можно. Я разрешаю.
Я подозреваю, что так-же как и результат любого другого запроса.
 
LONGMAN

LONGMAN

Dark Side of the Moon..
Спасибо что разрешили :) Вот не смог вывести запрос PROCEDURE ANALYZE(), вернул пустую строку..
 
Войдите или зарегистрируйтесь для ответа.
Сверху