Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / MySQL [игнор отключен] [закрыт для гостей] / How to avoid Using join buffer (Block Nested Loop)? / 4 сообщений из 4, страница 1 из 1
26.07.2013, 00:02:33
    #38344487
Shahriyar.R
Shahriyar.R
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
How to avoid Using join buffer (Block Nested Loop)?
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:31:21
    #38344505
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
How to avoid Using join buffer (Block Nested Loop)?
Тут плох не сам "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:55:48
    #38344513
Shahriyar.R
Shahriyar.R
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
How to avoid Using join buffer (Block Nested Loop)?
Wow ;)
Works greatly.
Thanks.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.07.2013, 01:26:11
    #38344519
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
How to avoid Using join buffer (Block Nested Loop)?
Кстати, этот метод описан в доке:
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
| Ответить | Цитировать | Написать  
Форумы / MySQL [игнор отключен] [закрыт для гостей] / How to avoid Using join buffer (Block Nested Loop)? / 4 сообщений из 4, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 362ms
Закрыть
start [/forum/topic.php?fid=47&mobile=1&tid=1836391]:
 0ms
get settings:
 10ms
get forum list:
 15ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 46ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 33ms
get tp. blocked users:
 1ms
others: 241ms
total:  362ms
x
x
Закрыть


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