ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Помогите с запросом
15 сообщений из 15, страница 1 из 1
Помогите с запросом
    #38715557
kageua
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kageua
Гость
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
SELECT i.url, i.manufacturer_images, i.manufacturer_videos, i.parsed_attributes , b.research_data_id, b.batch_id
FROM imported_data_parsed_new AS i
JOIN batch_to_urls AS b ON i.imported_data_id = b.imported_data_id
LEFT JOIN statistics_new AS sn ON b.imported_data_id = sn.imported_data_id
WHERE b.batch_id =1
AND (
    i.revision <> sn.revision
    OR sn.revision IS NULL
)
LIMIT 200



В таблицах batch_to_urls и statistics_new по ~750 тыс. записей. В imported_data_parsed_new ~550 тыс.
Данный запрос выполняется 13-14 секунд и выдает в результате 14 записей

Методом проб выяснил что тормоз тут
Код: sql
1.
i.revision <> sn.revision


При
Код: sql
1.
i.revision = sn.revision

, выдает около 750 тыс. записей за 0,005 с.

Подскажите, пожалуйста, хорошие книги или статьи по оптимизации.
Или каким образом можно улучшить время обработки такого запроса?
...
Рейтинг: 0 / 0
08.08.2014, 02:40:03
| Ответить | Цитировать | Написать  
Помогите с запросом
    #38715599
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Код: sql
1.
2.
3.
4.
AND (
    i.revision <> sn.revision
    OR sn.revision IS NULL
)


замените на
Код: sql
1.
AND ( NOT i.revision = sn.revision )


Кстати, и изначально OR sn.revision IS NULL можно было безболезненно убрать...
...
Рейтинг: 0 / 0
08.08.2014, 08:51:55
| Ответить | Цитировать | Написать  
Помогите с запросом
    #38715994
kageua
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kageua
Гость
К сожалению не помогло, время осталось тоже

Код: sql
1.
OR sn.revision IS NULL


все таки нужен
...
Рейтинг: 0 / 0
08.08.2014, 14:14:26
| Ответить | Цитировать | Написать  
Помогите с запросом
    #38716304
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
kageua,

1. лимит без ордера -- некузяво, нужен ли он вообше?

2. если спрашиваетет про скорость, по любому ВСЕГДА
выдавайте ЕХПЛАИН на все показаные запросы

3. если спрашиваетет про скорость,
по любому ВСЕГДА показывайте SHOW CREATE TABLE zzz для
всех таблиц (особо стеснительные могут заменить названия колонок что бы
ЦРУ не вычислило вас)

4. без #2 и #3 -- разговор безпредметный.
...
Рейтинг: 0 / 0
08.08.2014, 17:51:01
| Ответить | Цитировать | Написать  
Помогите с запросом
    #38716358
kageua
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kageua
Гость
javajdbc, спасибо за ответ. Вот данные

1) Лимит нужен, так как обрабатывается в несколько приёмов - каждый раз максимум по 200 строк

2) EXPLAIN
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1SIMPLEbrefbatch_id,imported_data_id,batch_id_2batch_id4const3586091SIMPLEsnrefimported_data_and_batch_id,imported_data_idimported_data_and_batch_id4test_db.b.imported_data_id11SIMPLEieq_refPRIMARYPRIMARY4test_db.b.imported_data_id1Using where

3) imported_data_parsed_new
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
CREATE TABLE `imported_data_parsed_new` (
 `imported_data_id` int(11) unsigned NOT NULL,
 `url` varchar(250) NOT NULL,
 `model` varchar(250) DEFAULT NULL,
 `revision` tinyint(3) unsigned DEFAULT NULL,
 `confidence` double(20,15) DEFAULT NULL,
 `hash` varchar(50) DEFAULT NULL,
 `manufacturer_images` int(5) DEFAULT NULL,
 `manufacturer_videos` int(5) DEFAULT NULL,
 `priceold` varchar(10) DEFAULT NULL,
 `date` datetime DEFAULT NULL,
 `description` text,
 `long_description` text,
 `parsed_attributes` text,
 `product_name` text,
 `features` text,
 `parsed_meta` text,
 `htags` text,
 `manufacturer_url` text,
 `anchors` text,
 `general_info` text,
 PRIMARY KEY (`imported_data_id`),
 KEY `url` (`url`),
 KEY `model` (`model`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8



statistics_new
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
CREATE TABLE `statistics_new` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `imported_data_id` int(11) NOT NULL,
 `batch_id` int(11) DEFAULT NULL,
 `research_data_id` int(11) DEFAULT NULL,
 `created` datetime NOT NULL,
 `revision` tinyint(3) unsigned NOT NULL,
 `short_description_wc` int(11) NOT NULL,
 `long_description_wc` int(11) NOT NULL,
 `title_keywords` varchar(8000) COLLATE utf8_unicode_ci NOT NULL,
 `own_price` float NOT NULL,
 `competitors_prices` text COLLATE utf8_unicode_ci NOT NULL,
 `price_diff` text COLLATE utf8_unicode_ci NOT NULL,
 `items_priced_higher_than_competitors` int(11) NOT NULL,
 `similar_products_competitors` text COLLATE utf8_unicode_ci NOT NULL,
 `manufacturer_info` text COLLATE utf8_unicode_ci NOT NULL,
 `category_id` int(11) DEFAULT '0',
 `brand_id` int(11) NOT NULL DEFAULT '0',
 `page_score` varchar(5) COLLATE utf8_unicode_ci DEFAULT 'c',
 `keyword_rankings` text COLLATE utf8_unicode_ci NOT NULL,
 `keyword_rankings_with_keywords` text COLLATE utf8_unicode_ci NOT NULL,
 `category_new_id` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `imported_data_and_batch_id` (`imported_data_id`,`batch_id`),
 KEY `imported_data_id` (`imported_data_id`),
 KEY `batch_id` (`batch_id`),
 KEY `research_data_id` (`research_data_id`),
 KEY `revision` (`revision`),
 KEY `category_id` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci



batch_to_urls
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
CREATE TABLE `batch_to_urls` (
 `batch_id` int(11) unsigned NOT NULL,
 `imported_data_id` int(11) unsigned NOT NULL,
 `research_data_id` int(11) unsigned NOT NULL,
 KEY `batch_id` (`batch_id`),
 KEY `imported_data_id` (`imported_data_id`),
 KEY `batch_id_2` (`batch_id`,`imported_data_id`),
 KEY `fk_batch_to_urls_research_data1_idx` (`research_data_id`),
 CONSTRAINT `fk_batch_to_urls_batches1` FOREIGN KEY (`batch_id`) REFERENCES `batches` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `fk_batch_to_urls_imported_data_parsed_new1` FOREIGN KEY (`imported_data_id`) REFERENCES `imported_data_parsed_new` (`imported_data_id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `fk_batch_to_urls_research_data1` FOREIGN KEY (`research_data_id`) REFERENCES `research_data` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
...
Рейтинг: 0 / 0
08.08.2014, 19:30:28
| Ответить | Цитировать | Написать  
Помогите с запросом
    #38716408
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
kageua,

1. сколько получается?
select count(1) from statistics where revision is null

2. чёй-то мне не нравится порядок подключения таблиц.
попробуйте

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
From
   batch_to_urls AS b
   STRAIGHT_JOIN imported_data_parsed_new AS i 
                             ON i.imported_data_id = b.imported_data_id
   LEFT JOIN statistics_new AS sn 
                   ON b.imported_data_id = sn.imported_data_id 
WHERE 
   b.batch_id =1
AND (
    i.revision <> sn.revision
    OR sn.revision IS NULL
)
...
Рейтинг: 0 / 0
08.08.2014, 22:14:09
| Ответить | Цитировать | Написать  
Помогите с запросом
    #38716415
kageua
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kageua
Гость
javajdbc,

1)
Код: sql
1.
SELECT count( 1 ) FROM statistics_new WHERE revision IS NULL


Результат 0 строк.
NULL получается при LEFT JOIN этой таблицы

2) Время не поменялось, +/- 0,1 с
EXPLAIN почти тот же
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1SIMPLEbrefbatch_id,imported_data_id,batch_id_2batch_id_24const3102881SIMPLEsnrefimported_data_and_batch_id,imported_data_idimported_data_and_batch_id4test_db.b.imported_data_id11SIMPLEieq_refPRIMARYPRIMARY4test_db.b.imported_data_id1Using where


Может профилирование что то прояснит?
starting0.000021Waiting for query cache lock0.000007checking query cache for query0.000056checking permissions0.000007checking permissions0.000005checking permissions0.000006Opening tables0.000035System lock0.000011Waiting for query cache lock0.000033init0.000025optimizing0.000020statistics0.001094preparing0.000022executing0.000006Sending data9.307438end0.000027query end0.000011closing tables0.000019freeing items0.000020Waiting for query cache lock0.000008freeing items0.000031Waiting for query cache lock0.000009freeing items0.000010storing result in query cache0.000039logging slow query0.000007cleaning up0.000008
...
Рейтинг: 0 / 0
08.08.2014, 22:54:03
| Ответить | Цитировать | Написать  
Помогите с запросом
    #38716427
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
kageua,

1. либо что-то не так, либо одно из двух! (С, Следствие ведут колобки)

С какой стати, имея заданый прямой жоин, база упорно
дает порядок подсоединения B, SN, I ????
Вы точно поставили STRAIGHT_JOIN ?


2. скорости и експлеин (внимательно где простой а где лефт жоин):

Код: sql
1.
2.
3.
4.
5.
6.
SELECT i.url, i.manufacturer_images, i.manufacturer_videos, i.parsed_attributes , b.research_data_id, b.batch_id
FROM imported_data_parsed_new AS i
JOIN batch_to_urls AS b ON i.imported_data_id = b.imported_data_id
JOIN statistics_new AS sn ON (b.imported_data_id = sn.imported_data_id AND i.revision <> sn.revision)
WHERE b.batch_id =1
LIMIT 200



и отдельно:

Код: sql
1.
2.
3.
4.
5.
6.
7.
SELECT i.url, i.manufacturer_images, i.manufacturer_videos, i.parsed_attributes , b.research_data_id, b.batch_id
FROM imported_data_parsed_new AS i
JOIN batch_to_urls AS b ON i.imported_data_id = b.imported_data_id
LEFT JOIN statistics_new AS sn ON b.imported_data_id = sn.imported_data_id
WHERE b.batch_id =1
         AND sn.revision IS NULL
LIMIT 200
...
Рейтинг: 0 / 0
08.08.2014, 23:50:02
| Ответить | Цитировать | Написать  
Помогите с запросом
    #38716429
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
kageua,

Sending data 9.307438

ничего интересного --- это стандартный стейт
для чтения, сборки и посылки результата
...
Рейтинг: 0 / 0
08.08.2014, 23:52:59
| Ответить | Цитировать | Написать  
Помогите с запросом
    #38716684
kageua
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kageua
Гость
javajdbc,

1) Наверное что то перепутал - попробовал сейчас, порядок как вы сказали
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1SIMPLEbrefbatch_id,imported_data_id,batch_id_2batch_id4const3507701SIMPLEieq_refPRIMARYPRIMARY4test_db.b.imported_data_id11SIMPLEsnrefimported_data_and_batch_id,imported_data_idimported_data_and_batch_id4test_db.b.imported_data_id1Using where

2) 9.7743 сек.
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1SIMPLEbrefbatch_id,imported_data_id,batch_id_2batch_id4const3621191SIMPLEsnrefimported_data_and_batch_id,imported_data_idimported_data_and_batch_id4test_db.b.imported_data_id1Using where1SIMPLEieq_refPRIMARYPRIMARY4test_db.sn.imported_data_id1Using where

9.6673 сек.
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1SIMPLEbrefbatch_id,imported_data_id,batch_id_2batch_id4const3809561SIMPLEieq_refPRIMARYPRIMARY4test_db.b.imported_data_id11SIMPLEsnrefimported_data_and_batch_id,imported_data_idimported_data_and_batch_id4test_db.b.imported_data_id1Using where, Not exists

Первоначально, в imported_data_parsed_new revision был varchar(4)
В statistics_new tinyint(4)
Результат был 13-14 с секунд
После того, как поставил оба unsigned tinyint(3), результат стал 10-11 секунд
Могут ли быть какие то серъёзные изменения, при добавлении ключей и т.п.?
...
Рейтинг: 0 / 0
09.08.2014, 23:59:36
| Ответить | Цитировать | Написать  
Помогите с запросом
    #38716699
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
kageua,

1. у вас нет ПРИМАРИ КЕЙ на таблице batch_to_urls.
Хорошо ли это?

2. если никаких подвохов со стороны
базы, то похоже что перебор трети милионов
записей в 3 таблица на вашем железе таки
занимает 10 секунд.

3. попробуем зайти сбоку. Проверьте ,
скорость и експлейн и сколько записей ??? :

Код: sql
1.
2.
3.
4.
5.
6.
SELECT imported_data_id
FROM imported_data_parsed_new AS i
LEFT JOIN statistics_new AS sn ON i.imported_data_id = sn.imported_data_id
WHERE 
    i.revision <> sn.revision
    OR sn.revision IS NULL



4. если скорость меньше 5 секунд и записей меньше
10 тысяч, то имеет смысл развить эту тему --
подключить batch_to_urls как третью таблицу а не как первую.

5. мне понравилось как анти-жоинт высветился как NOT EXISTS в EXPLАIN-EXTRA
...
Рейтинг: 0 / 0
10.08.2014, 01:03:06
| Ответить | Цитировать | Написать  
Помогите с запросом
    #38716757
kageua
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kageua
Гость
javajdbc,

1) batch_to_urls это просто соединительная таблица, для соединения 3 других таблиц. Состоит просто из 3 полей, которые PRIMARY в своих таблицах.

2) Сайт сейчас находится на Amazon EC2 t2.micro. Виртуально одно ядро процессора Intel Xeon 2,5 Ghz с 1 GB памяти. Возможно памяти и маловато

3) 6.2744 сек. - 16 записей (первоначальный запрос выдаёт 14 записей - 10 от <> и 4 IS NULL)
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1SIMPLEiALLNULLNULLNULLNULL7910681SIMPLEsnrefimported_data_and_batch_id,imported_data_idimported_data_and_batch_id4test_db.i.imported_data_id1Using where

4) При подключении через обычный JOIN batch_to_urls, получил

SELECT i.imported_data_id
FROM imported_data_parsed_new AS i
LEFT JOIN statistics_new AS sn ON i.imported_data_id = sn.imported_data_id
JOIN batch_to_urls AS b ON b.imported_data_id = i.imported_data_id
AND b.batch_id =1
WHERE i.revision <> sn.revision
OR sn.revision IS NULL

6.6725 сек.

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1SIMPLEbrefbatch_id,imported_data_id,batch_id_2batch_id_24const367267Using index1SIMPLEieq_refPRIMARYPRIMARY4test_db.b.imported_data_id11SIMPLEsnrefimported_data_and_batch_id,imported_data_idimported_data_and_batch_id4test_db.i.imported_data_id1Using where

Если же в SELECT указать все нужные поля, то приходим опять к 9.8 секунд


Ещё один вопрос - в statistics_new revision указан, как ключ, в imported_data_parsed_new такого нет
Может индексирование во второй таблице улучшить время?
...
Рейтинг: 0 / 0
10.08.2014, 12:06:26
| Ответить | Цитировать | Написать  
Помогите с запросом
    #38716822
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
kageua,

1. если устраивает 6 секунд, то подсоединить бач-2-урл просто:


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
select ..........................
from
(
SELECT imported_data_id, i.url, i.manufacturer_images, i.manufacturer_videos, i.parsed_attributes
FROM imported_data_parsed_new AS i
LEFT JOIN statistics_new AS sn ON i.imported_data_id = sn.imported_data_id
WHERE 
    i.revision <> sn.revision
    OR sn.revision IS NULL
) zz
join batch2url b ON b.batch_id = 1 and b.imported_data_id = zz.imported_data_id




2. Примари-Кей -- на всякий случай поэкспериментируйте --
добавьте его и посмотрите снова на скорость исходного СКЛа.


3. добавьте ключ на STATISTICS_NEW
KEY `imported_data_id_revision` (imported_data_id,revision),

Посмотрите ускорится ли базовый 6 секундный СКЛ

4. по логике, может ли в СТАТАТИСТИК_НЮ быть
больше одной записи для imported_data_id ?

Есле НЕ может быть больше одной, то попробуйте:

Код: sql
1.
2.
3.
4.
5.
6.
7.
SELECT imported_data_id, i.url, i.manufacturer_images, i.manufacturer_videos, i.parsed_attributes
FROM imported_data_parsed_new AS i
WHERE
  NOT EXISTS
(select 1 from statistics_new sn 
where  i.imported_data_id = sn.imported_data_id
AND i.revision = sn.revision)



5. по логике базы, какой смысл имеет
batch_id в таблице СН и в таблице Б2У ?
...
Рейтинг: 0 / 0
10.08.2014, 18:08:46
| Ответить | Цитировать | Написать  
Помогите с запросом
    #38719212
kageua
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kageua
Гость
javajdbc,

1) По времени этот запрос не отличается от исходного. Но что странно на другой базе это заняло вообще около 2 минут.
Так что остановлюсь на первом варианте, он быстрее исходного и работает и там и там

2) Время не поменялось

3) Время не поменялось

4) Да, такое возможно

5) Смысл один и тот же, это ключ в таблице batches
...
Рейтинг: 0 / 0
13.08.2014, 12:33:48
| Ответить | Цитировать | Написать  
Помогите с запросом
    #38719690
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
kageua,


для очистки совести, проверьте скорости таких СКЛ:

Код: sql
1.
2.
3.
4.
5.
6.
7.
SELECT  SQL_NO_CACHE
    imported_data_id, i.url, i.manufacturer_images, i.manufacturer_videos, i.parsed_attributes
FROM imported_data_parsed_new AS i
WHERE
  NOT EXISTS
(select 1 from statistics_new sn 
where  i.imported_data_id = sn.imported_data_id)




Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
SELECT SQL_NO_CACHE 
     imported_data_id, i.url, i.manufacturer_images, i.manufacturer_videos, i.parsed_attributes
FROM imported_data_parsed_new AS i
WHERE
   EXISTS
(select 1 from statistics_new sn 
where  i.imported_data_id = sn.imported_data_id
AND i.revision != sn.revision)
...
Рейтинг: 0 / 0
13.08.2014, 17:46:18
| Ответить | Цитировать | Написать  
15 сообщений из 15, страница 1 из 1
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Помогите с запросом
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=47&gotonew=1&tid=1834373]:
 0ms
get settings:
 9ms
get forum list:
 15ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 47ms
get topic data:
 9ms
get first new msg:
 5ms
get forum data:
 4ms
get page messages:
 39ms
get tp. blocked users:
 1ms
others: 246ms
total:  383ms
созд. / загр.: 383ms
x
x
Закрыть


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