ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Extra: Range checked for each record (index map: 0x1)
6 сообщений из 6, страница 1 из 1
Extra: Range checked for each record (index map: 0x1)
    #38398622
Shahriyar.R
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shahriyar.R
Гость
Yesterday i accomplished another interesting question with great help of Cygapb-007 .
Thanks again.
See: Previous Topic

But for my final query i have an Execution Plan with : Extra: Range checked for each record (index map: 0x1)

I haven't encountered before such Extra clause. Thats why the question is simply: What is it? (INTERESTED especially in this clause)

Final Query:

Код: 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.
select 
  t.name,
  count(*) as total_matchs,
  sum(g.wins>0) as wins,
  sum(g.wins=0) as draw,
  sum(g.wins<0) as lost,
  sum(case sign(g.wins)
    when 1 then 3
    when 0 then 1
    else 0 end) as total_point,
  sum(g.GF) as GF,
  sum(g.GA)as GA,
  sum(g.GF)-sum(g.GA) as average
from (
  select 
    t.id, 
	l.match, 
    sum(if(t.id=l.team,1,-1)) as wins,
    sum(t.id=l.team) as GF,
    sum(t.id<>l.team) as GA
  from wd_teams t
  join wd_matches m on t.id in (m.home, m.away)
  join wd_timeline l on l.`match`=m.id and l.goal=1
  group by t.id, l.match
  )g
join wd_teams t on t.id=g.id
group by t.id, t.name
order by total_point desc,average desc;




Query QEP:

Код: 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.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra: Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ref
possible_keys: <auto_key0>
          key: <auto_key0>
      key_len: 4
          ref: tplaz_webdreams.t.id
         rows: 13
        Extra: NULL
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: l
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 139
        Extra: Using where; Using temporary; Using filesort
*************************** 4. row ***************************
           id: 2
  select_type: DERIVED
        table: m
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: tplaz_webdreams.l.match
         rows: 1
        Extra: Using where
*************************** 5. row ***************************
           id: 2
  select_type: DERIVED
        table: t
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra: Range checked for each record (index map: 0x1)
5 rows in set (0.00 sec)




Table structures:
wd_timeline

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
CREATE TABLE `wd_timeline` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `match` int(11) NOT NULL,
  `type` tinyint(3) unsigned NOT NULL,
  `team` int(11) NOT NULL,
  `player1` int(11) unsigned NOT NULL,
  `player2` int(10) unsigned NOT NULL,
  `goal` tinyint(1) DEFAULT NULL,
  `time` tinyint(1) DEFAULT NULL,
  `minute` tinyint(4) NOT NULL,
  `plus` tinyint(4) NOT NULL,
  `about` varchar(250) NOT NULL,
  `ext` char(5) NOT NULL,
  `status` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=140 DEFAULT CHARSET=utf8



wd_teams

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
CREATE TABLE `wd_teams` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `slug` varchar(25) NOT NULL,
  `name` varchar(250) NOT NULL,
  `about` text NOT NULL,
  `site` varchar(250) NOT NULL,
  `founded` date NOT NULL,
  `country` smallint(5) unsigned NOT NULL,
  `city` mediumint(8) unsigned NOT NULL,
  `stadium` int(11) NOT NULL,
  `ext` varchar(5) NOT NULL,
  `status` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8




wd_matches
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
CREATE TABLE `wd_matches` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `home` int(11) NOT NULL,
  `away` int(11) NOT NULL,
  `type` tinyint(1) NOT NULL,
  `stadium` smallint(5) unsigned NOT NULL,
  `referee` mediumint(8) unsigned NOT NULL,
  `match_date` datetime NOT NULL,
  `about` text NOT NULL,
  `ext` char(5) NOT NULL,
  `status` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8
...
Рейтинг: 0 / 0
17.09.2013, 12:05:46
| Ответить | Цитировать | Написать  
Extra: Range checked for each record (index map: 0x1)
    #38398657
tanglir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tanglir
Участник
Shahriyar.R What is it? In short, that shows that there are no index that can be used directly , but there is some probability of using some of table indices at least for some records.

FYI, if you open this link http://lmgtfy.com/?q=mysql Range checked for each record
and follow the very first link there: http://dev.mysql.com/doc/refman/5.1/en/explain-output.html
Then press Ctrl-F, "range c", Enter, you'll find this:
авторRange checked for each record (index map: N)

MySQL found no good index to use, but found that some of indexes might be used after column values from preceding tables are known. For each row combination in the preceding tables, MySQL checks whether it is possible to use a range or index_merge access method to retrieve rows. This is not very fast, but is faster than performing a join with no index at all. The applicability criteria are as described in Section 8.3.1.3, “Range Optimization”, and Section 8.3.1.4, “Index Merge Optimization”, with the exception that all column values for the preceding table are known and considered to be constants.

Indexes are numbered beginning with 1, in the same order as shown by SHOW INDEX for the table. The index map value N is a bitmask value that indicates which indexes are candidates. For example, a value of 0x19 (binary 11001) means that indexes 1, 4, and 5 will be considered.
...
Рейтинг: 0 / 0
17.09.2013, 12:20:27
| Ответить | Цитировать | Написать  
Extra: Range checked for each record (index map: 0x1)
    #38398931
Shahriyar.R
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shahriyar.R
Гость
tanglir,

:D truely i have already read documentation...before your link :)
I think i ask wrong question sorry...
Exactly what i want:
lets extract This - "DERIVED" query from full query and test it . Extracted from full query:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
select 
    t.id, 
	l.match, 
    sum(if(t.id=l.team,1,-1)) as wins,
    sum(t.id=l.team) as GF,
    sum(t.id<>l.team) as GA
  from wd_teams t
  join wd_matches m on t.id in (m.home, m.away)
  join wd_timeline l on l.`match`=m.id and l.goal=1
  group by t.id, l.match



QEP of extracted query:

Код: 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.
33.
34.
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: l
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 139
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: m
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: tplaz_webdreams.l.match
         rows: 1
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra: Range checked for each record (index map: 0x1)
3 rows in set (0.00 sec)



As documentation state:
it is from table t = wd_team and index map is : 0x1 = 1. it will check sequentally from SHOW INDEX:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
mysql> show index from wd_teams\G
*************************** 1. row ***************************
        Table: wd_teams
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 10
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
1 row in set (0.00 sec)



1. So it considers to use PK from t/wd_teams?.(after column values from preceding tables are known)

For testing purpose i tried to ignore this PK:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
select 
    t.id, 
	l.match, 
    sum(if(t.id=l.team,1,-1)) as wins,
    sum(t.id=l.team) as GF,
    sum(t.id<>l.team) as GA
  from wd_teams t ignore index(primary)
  join wd_matches m on t.id in (m.home, m.away)
  join wd_timeline l on l.`match`=m.id and l.goal=1
  group by t.id, l.match;




QEP changed :

Код: 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.
33.
34.
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: l
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 139
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: m
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: tplaz_webdreams.l.match
         rows: 1
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra: Using where; Using join buffer (Block Nested Loop)
3 rows in set (0.00 sec)



Exactly it changed from Range Checked to BNL.

and added some indexes:

Код: sql
1.
2.
alter table wd_timeline add index(`match`,goal,team);
alter table wd_matches add index(home,away);



QEP changed one more time it seems not bad except BNL:

Код: 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.
33.
34.
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: m
         type: index
possible_keys: PRIMARY
          key: home
      key_len: 8
          ref: NULL
         rows: 30
        Extra: Using index; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: l
         type: ref
possible_keys: match
          key: match
      key_len: 6
          ref: tplaz_webdreams.m.id,const
         rows: 3
        Extra: Using where; Using index
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra: Using where; Using join buffer (Block Nested Loop)
3 rows in set (0.00 sec)




2. So for this purpose which one i must choose? BNL over Range Check? is there any exact rule?
Another question is how i can avoid both Range Check and BNL here? maybe some tricks.

Thanks.
...
Рейтинг: 0 / 0
17.09.2013, 14:42:33
| Ответить | Цитировать | Написать  
Extra: Range checked for each record (index map: 0x1)
    #38399000
tanglir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tanglir
Участник
Shahriyar.R So it considers to use PK from t/wd_teams? yes, at least if documentation is correct )
Shahriyar.R So for this purpose which one i must choose? BNL over Range Check? is there any exact rule? dont realy know, but using join buffer means no index is used at all while RC means they at least could be used... you better look at actual query time and decide for yourself
Shahriyar.R Another question is how i can avoid both Range Check and BNL here? maybe some tricks. one way or another, but you must join 3 tables, and using not just equality/comparison conditions... can't think of any trick in this situation.

what if you try to calculate this way nah, i was going to propose the same method that was rejected in previous topic :)
...
Рейтинг: 0 / 0
17.09.2013, 15:24:20
| Ответить | Цитировать | Написать  
Extra: Range checked for each record (index map: 0x1)
    #38399001
Cygapb-007
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Cygapb-007
Участник
[off]
MS SQL
[/off]
...
Рейтинг: 0 / 0
17.09.2013, 15:24:56
| Ответить | Цитировать | Написать  
Extra: Range checked for each record (index map: 0x1)
    #38399062
Shahriyar.R
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shahriyar.R
Гость
Cygapb-007,

I changed query as you wrote:

Код: 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.
select 
  t.name,
  count(*) as total_matchs,
  sum(case when g.wins>0 then 1 else 0 end) as wins,
  sum(case when g.wins=0 then 1 else 0 end) as draw,
  sum(case when g.wins=0 then 1 else 0 end) as lost,
  sum(case sign(g.wins)
    when 1 then 3
    when 0 then 1
    else 0 end) as total_point,
  sum(g.GF) as GF,
  sum(g.GA)as GA,
  sum(g.GF)-sum(g.GA) as average
from (
  select 
    t.id, 
	l.match, 
	sum(case when t.id=l.team then 1 else -1 end) as wins,
	sum(case when t.id=l.team then 1 else 0 end) as GF,
	sum(case when t.id=l.team then 0 else 1 end) as GA
  from wd_teams t
  join wd_matches m on t.id in (m.home, m.away)
  join wd_timeline l on l.`match`=m.id and l.goal=1
  group by t.id, l.match
  )g 
inner join wd_teams t on t.id=g.id
group by t.id, t.name
order by total_point desc,sum(g.GF)-sum(g.GA)  desc;



QEP doesnt changed:

Код: 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.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra: Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ref
possible_keys: <auto_key0>
          key: <auto_key0>
      key_len: 4
          ref: tplaz_webdreams.t.id
         rows: 10
        Extra: NULL
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: m
         type: index
possible_keys: PRIMARY
          key: home
      key_len: 8
          ref: NULL
         rows: 30
        Extra: Using index; Using temporary; Using filesort
*************************** 4. row ***************************
           id: 2
  select_type: DERIVED
        table: l
         type: ref
possible_keys: match
          key: match
      key_len: 6
          ref: tplaz_webdreams.m.id,const
         rows: 3
        Extra: Using where; Using index
*************************** 5. row ***************************
           id: 2
  select_type: DERIVED
        table: t
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra: Range checked for each record (index map: 0x1)
5 rows in set (0.00 sec)



I also attached visual explain plan:

...
Рейтинг: 0 / 0
17.09.2013, 16:07:34
| Ответить | Цитировать | Написать  
6 сообщений из 6, страница 1 из 1
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Extra: Range checked for each record (index map: 0x1)
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=47&msg=38398931&tid=1836034]:
 0ms
get settings:
 7ms
get forum list:
 17ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 45ms
get topic data:
 7ms
get forum data:
 1ms
get page messages:
 33ms
get tp. blocked users:
 1ms
others: 202ms
total:  319ms
созд. / загр.: 319ms
x
x
Закрыть


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