ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / How to avoid Using join buffer (Block Nested Loop)?
4 сообщений из 4, страница 1 из 1
How to avoid Using join buffer (Block Nested Loop)?
    #38344487
Shahriyar.R
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shahriyar.R
Гость
dear experts,
i have an interesting issue that i cant resolve at moment.
Using:
MySQL 5.6.12 + default my.cnf parameters
Centos 6.4
Tables:

Код: 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.
31.
32.
-- wd_operations

CREATE TABLE `wd_operations` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `transaction` int(11) unsigned NOT NULL,
  `mid` int(8) unsigned zerofill NOT NULL,
  `percent` float NOT NULL,
  `bon` float NOT NULL,
  `operation_date` datetime NOT NULL,
  `type` tinyint(2) unsigned NOT NULL,
  `status` tinyint(1) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `mid` (`mid`),
  KEY `transaction` (`transaction`)
) ENGINE=InnoDB AUTO_INCREMENT=36410 DEFAULT CHARSET=utf8

-- wd_transactions

 CREATE TABLE `wd_transactions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mid` int(8) unsigned zerofill NOT NULL,
  `partner` smallint(3) unsigned zerofill NOT NULL,
  `sum` float NOT NULL,
  `percent` int(11) NOT NULL,
  `general` float NOT NULL,
  `client` float NOT NULL,
  `transaction_date` datetime NOT NULL,
  `type` tinyint(1) unsigned NOT NULL,
  `status` tinyint(1) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `mid` (`mid`)
) ENGINE=InnoDB AUTO_INCREMENT=7409 DEFAULT CHARSET=utf8



First query is :

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
select 
    count(*)
from
    wd_operations a,
    wd_transactions b
where
    (a.mid = 01460641 AND a.transaction = b.id);

+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)




Explain plan looks good:

Код: 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.
mysql> explain select      count(*) from     wd_operations a,     wd_transactions b where     (a.mid = 01460641 AND a.transaction = b.id)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
         type: ref
possible_keys: mid,transaction
          key: mid
      key_len: 4
          ref: const
         rows: 1
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: new_multibon.a.transaction
         rows: 1
        Extra: Using where; Using index
2 rows in set (0.00 sec)



But when i add another clause to where:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
select 
    count(*)
from
    wd_operations a,
    wd_transactions b
where
    ((a.mid = 01460641
        AND a.transaction = b.id)
        OR b.mid = 01460641);


+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (29.59 sec)



i takes approximately 25-29 seconds. Thinking that this query is a part of big one it will take much more time to execute. So i decided to break down into simple queries and then combine all optimization techniques together and need here your helps.

Explain plan became worse than ever:

Код: 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.
mysql> explain select      count(*) from     wd_operations a,     wd_transactions b where     ((a.mid = 01460641         AND a.transaction = b.id)         OR b.mid = 01460641)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
         type: index
possible_keys: PRIMARY,mid
          key: mid
      key_len: 4
          ref: NULL
         rows: 7451
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
         type: ALL
possible_keys: mid,transaction
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 35386
        Extra: Using where; Using join buffer (Block Nested Loop)
2 rows in set (0.00 sec)




So is there any tips to avoid Using join buffer (Block Nested Loop) especially for my purpose?
Need any help,suggest. thanks.
...
Рейтинг: 0 / 0
26.07.2013, 00:02:33
| Ответить | Цитировать | Написать  
How to avoid Using join buffer (Block Nested Loop)?
    #38344505
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
Тут плох не сам "Using join buffer" (он лишь следствие), а невозможность использования индекса для соединения таблиц.
Методика, в общем-то, стандартная - "развалить" запрос на два с заменой OR на UNION/UNION ALL.
Примерно так:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
SELECT SUM(cnt) FROM (
select 
    count(*) cnt
from
    wd_operations a,
    wd_transactions b
where
    a.mid = 01460641 AND a.transaction = b.id and b.mid <> 01460641
UNION ALL
select 
    count(*) cnt
from
    wd_operations a,
    wd_transactions b
where
    b.mid = 01460641) t

Правда, из-за изначальной странности логики исходного запроса тут образуется странное место в виде декартового произведения во второй части запроса. Тут уж нужно по предметной области смотреть, насколько это правильно.
...
Рейтинг: 0 / 0
26.07.2013, 00:31:21
| Ответить | Цитировать | Написать  
How to avoid Using join buffer (Block Nested Loop)?
    #38344513
Shahriyar.R
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shahriyar.R
Гость
Wow ;)
Works greatly.
Thanks.
...
Рейтинг: 0 / 0
26.07.2013, 00:55:48
| Ответить | Цитировать | Написать  
How to avoid Using join buffer (Block Nested Loop)?
    #38344519
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
Кстати, этот метод описан в доке:
http://dev.mysql.com/doc/refman/5.5/en/how-to-avoid-table-scan.html Minimize the OR keywords in your WHERE clauses. If there is no index that helps to locate the values on both sides of the OR, any row could potentially be part of the result set, so all rows must be tested, and that requires a full table scan. If you have one index that helps to optimize one side of an OR query, and a different index that helps to optimize the other side, use a UNION operator to run separate fast queries and merge the results afterward.
...
Рейтинг: 0 / 0
26.07.2013, 01:26:11
| Ответить | Цитировать | Написать  
4 сообщений из 4, страница 1 из 1
Форумы / MySQL [игнор отключен] [закрыт для гостей] / How to avoid Using join buffer (Block Nested Loop)?
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=47&fpage=217&tid=1836391]:
 0ms
get settings:
 9ms
get forum list:
 13ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 45ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 35ms
get tp. blocked users:
 1ms
others: 238ms
total:  359ms
созд. / загр.: 359ms
x
x
Закрыть


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