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

[WildZero]

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

Добрый день. Подскажите пожалуйста.
В цикле выполняется следующий запрос:
"UPDATE goods SET status='".$tmp[2]."' WHERE (goods_catalog=2 AND goods_article=".$tmp[0]." AND goods_size=".$size.")"

кол-во повторений - 3 000 000.
кол-во строк в таблице - 2 500 000.
Среднее время выполнения одного запроса 5 сек.
Каким образом можно ускорить данный процесс?

goods_catalog - int(10)
goods_article - varchar(50)
goods_size - varchar(50)

 

[zerkms]

структуру таблицы в студию

навскидку: сделать составной индекс на все три перечисленных поля в следующем порядке:

goods_catalog + goods_size + goods_article

 

[WildZero]

goods_id int(10) Нет
goods_catalog int(10) Да NULL
goods_provider int(10) Да NULL
goods_article varchar(50) Нет
goods_name varchar(100) Нет
goods_namerus varchar(100) Нет
goods_color varchar(50) Нет
goods_size varchar(50) Нет
goods_width double(15,2) Да 0.00
goods_height double(15,2) Да 0.00
goods_length double(15,2) Да 0.00
goods_weight double(15,2) Да 0.00
goods_volume double(15,2) Да 0.00
goods_price double(15,2) Да 0.00
goods_24h tinyint(1) Да NULL
goods_present tinyint(1) Да NULL
goods_comment varchar(100) Нет
goods_guarantee varchar(10) Нет
status int(1)

-~{}~ 01.03.10 15:16:

А индекс делал, не помогло.

 

[KR]

WildZero, это не структура таблицы
SHOW CREATE TABLE $tablename;

 

[zerkms]

повторю: структура таблицы с уже созданным индексом, который я посоветовал + пример чистого от пхп запроса в plain sql.

 

[WildZero]

CREATE TABLE `goods` (
`goods_id` int(10) NOT NULL auto_increment,
`goods_catalog` int(10) default NULL,
`goods_provider` int(10) default NULL,
`goods_article` varchar(50) NOT NULL default '',
`goods_name` varchar(100) NOT NULL default '',
`goods_namerus` varchar(100) NOT NULL default '',
`goods_color` varchar(50) NOT NULL default '',
`goods_size` varchar(50) NOT NULL default '',
`goods_width` double(15,2) default '0.00',
`goods_height` double(15,2) default '0.00',
`goods_length` double(15,2) default '0.00',
`goods_weight` double(15,2) default '0.00',
`goods_volume` double(15,2) default '0.00',
`goods_price` double(15,2) default '0.00',
`goods_24h` tinyint(1) unsigned default NULL,
`goods_present` tinyint(1) unsigned default NULL,
`goods_comment` varchar(100) NOT NULL default '',
`goods_guarantee` varchar(10) NOT NULL default '',
`status` int(1) NOT NULL,
PRIMARY KEY (`goods_id`),
KEY `goods_catalog` (`goods_catalog`,`goods_provider`,`goods_article`,`goods_present`),
KEY `goods_catalog_2` (`goods_catalog`),
KEY `goods_article` (`goods_article`),
KEY `goods_size` (`goods_size`),
KEY `goods_name` (`goods_name`),
KEY `constraint_name_catalog` (`goods_name`,`goods_catalog`),
FULLTEXT KEY `goods_article_2` (`goods_article`),
FULLTEXT KEY `goods_article_3` (`goods_article`)
) ENGINE=MyISAM AUTO_INCREMENT=3626693 DEFAULT CHARSET=utf8
""

UPDATE goods SET status='1' WHERE (goods_catalog=2 AND goods_article=103527 AND goods_size=40

 

[zerkms]

EXPLAIN SELECT goods_catalog FROM goods WHERE goods_catalog=2 AND goods_article=103527 AND goods_size=40


сюда результаты.

 

[WildZero]

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE goods ref goods_catalog,goods_catalog_2,goods_article,goods_size,goods_article_2,goods_article_3 goods_catalog 5 const 1090944 Using where

 

[magic]

А чё goods_article & goods_size определены как varchar?

При апдейтах индексы можно отключить, хотя status у тебя вроде не индексирован.

 

[WildZero]

goods_article и goods_size могут содержать текстовые символы.

 

[zerkms]

SELECT COUNT(goods_catalog) FROM goods WHERE goods_catalog=2 AND goods_article=103527 AND goods_size=40


выдаст 1090944 ???????

-~{}~ 01.03.10 22:44:

goods_article и goods_size могут содержать текстовые символы.

где тогда кавычки в первом посте?

 

[WildZero]

SELECT COUNT(goods_catalog) FROM goods WHERE goods_catalog=2 AND goods_article=103527 AND goods_size=40
Выдаёт 1.
В данном запросе нет текстовых артикулов и размеров.

 

[zerkms]

тогда почему

1 SIMPLE goods ref goods_catalog,goods_catalog_2,goods_article,goods_size,goods_article_2,goods_article_3 goods_catalog 5 const 1090944 Using where


сделай ANALYZE + OPTIMIZE для таблы

 

[WildZero]

ANALYZE:
Table Op Msg_type Msg_text
h_vexler-db1.goods analyze status Table is already up to date

OPTIMIZE
Table Op Msg_type Msg_text
h_vexler-db1.goods optimize status OK

 

[zerkms]

и повтори запрос с EXPLAIN

(только результаты обрами в теги code, чтобы читабельно было)

 

[WildZero]

EXPLAIN SELECT goods_catalog FROM goods WHERE goods_catalog=2 AND goods_article='103527' AND goods_size='40'

1	SIMPLE	goods	ref	goods_catalog,goods_catalog_2,goods_article,goods_size,goods_article_2,goods_article_3	goods_article	152	const	14	Using where

с кавычками

 

[zerkms]

ну вот. уже сильно лучше.
теперь всё должно летать. не?

не то что летать, но работать заметно быстрее

-~{}~ 01.03.10 23:04:

FULLTEXT KEY `goods_article_2` (`goods_article`),
FULLTEXT KEY `goods_article_3` (`goods_article`)

KEY `goods_catalog` (`goods_catalog`,`goods_provider`,`goods_article`,`goods_present`),
KEY `goods_catalog_2` (`goods_catalog`),

KEY `goods_name` (`goods_name`),
KEY `constraint_name_catalog` (`goods_name`,`goods_catalog`),

дубликаты. удалить.

 

[WildZero]

Отлично! Всё работает на ура! Спасибо.

 

[Wicked]

zerkms

дубликаты. удалить.

а ведь он пойдет и удалит все 6 индексов, не?

 

[WildZero]

А можно немного теории?
Почему при постановке кавычек запрос стал выполняться в разы быстрее??