Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.5, 10.6, 10.7(EOL), 10.8(EOL)
Description
Filing this in connection with MDEV-27366.
This MDEV entry doesn't need a fix, it is just to record a proof that join_cache.result contained meaningless query plans.
join_cache.result shows query plan that use Rowid Filtering where it obviously doesn't make any sense to do it.
I claim that all EXPLAIN output at these lines do not make sense:
856:1 SIMPLE CountryLanguage hash_ALL|filter PRIMARY,Percentage #hash#PRIMARY|Percentage 3|4 world.City.Country 984 (19%) Using where; Using join buffer (flat, BNLH join); Using rowid filter
|
1056:1 SIMPLE CountryLanguage hash_ALL|filter PRIMARY,Percentage #hash#PRIMARY|Percentage 3|4 world.City.Country 984 (19%) Using where; Using join buffer (incremental, BNLH join); Using rowid filter
|
2107:1 SIMPLE CountryLanguage hash_ALL|filter PRIMARY,Percentage #hash#PRIMARY|Percentage 3|4 world.City.Country 984 (19%) Using where; Using join buffer (flat, BNLH join); Using rowid filter
|
2211:1 SIMPLE CountryLanguage hash_ALL|filter PRIMARY,Percentage #hash#PRIMARY|Percentage 3|4 world.City.Country 984 (19%) Using where; Using join buffer (incremental, BNLH join); Using rowid filter
|
Let's analyze the first case. I'm attaching a smaller test file that contains just that query.
Running it, one can see:
id 1
|
select_type SIMPLE
|
table CountryLanguage
|
type hash_ALL|filter
|
possible_keys PRIMARY,Percentage
|
key #hash#PRIMARY|Percentage
|
key_len 3|4
|
ref world.City.Country
|
rows 984 (19%)
|
Extra Using where; Using join buffer (flat, BNLH join); Using rowid filter
|
This means it is going to use Rowid Filter together with a full table (not index!) scan. The table is MyISAM.
Relevant portion of ANALYZE output:
"table": {
|
"table_name": "CountryLanguage",
|
"access_type": "hash_ALL",
|
"possible_keys": ["PRIMARY", "Percentage"],
|
"key": "#hash#PRIMARY",
|
"key_length": "3",
|
"used_key_parts": ["Country"],
|
"ref": ["world.City.Country"],
|
"rowid_filter": {
|
"range": {
|
"key": "Percentage",
|
"used_key_parts": ["Percentage"]
|
},
|
"rows": 185,
|
"selectivity_pct": 18.80081301,
|
"r_rows": 168,
|
"r_selectivity_pct": 0,
|
"r_buffer_size": 147,
|
"r_filling_time_ms": 0.086968316
|
},
|
"r_loops": 1,
|
"rows": 984,
|
"r_rows": 984,
|
"r_table_time_ms": 0.187748105,
|
"r_other_time_ms": 0.200407506,
|
"filtered": 18.80081367,
|
"r_filtered": 17.07317073,
|
"attached_condition": "CountryLanguage.Percentage > 50"
|
},
|
Note that
- rowid filter is indeed built
- Then, it is not used: it has r_selectivity_pct=0, but the table has r_rows>0 and r_filtered>0. This can only occur when filter was not used.
I've verified in debugger that CountryLanguage is read using full table (not index) scan:
#1 0x0000555556a6353e in ha_myisam::rnd_next (this=0x7fffd402d540, buf=0x7fffd402d0e0 "\377AFGPashto", ' ' <repeats 24 times>, "\232\231QB", '\245' <repeats 42 times>, "\240\321\002\324\377\177") at /home/psergey/dev-git2/10.5-look/storage/myisam/ha_myisam.cc:2041
|
#2 0x00005555561f5336 in handler::ha_rnd_next (this=0x7fffd402d540, buf=0x7fffd402d0e0 "\377AFGPashto", ' ' <repeats 24 times>, "\232\231QB", '\245' <repeats 42 times>, "\240\321\002\324\377\177") at /home/psergey/dev-git2/10.5-look/sql/handler.cc:3096
|
#3 0x00005555563b249b in rr_sequential (info=0x7fffd40c6810) at /home/psergey/dev-git2/10.5-look/sql/records.cc:519
|
#4 0x0000555555dcad93 in READ_RECORD::read_record (this=0x7fffd40c6810) at /home/psergey/dev-git2/10.5-look/sql/records.h:80
|
#5 0x00005555560822ab in JOIN_TAB_SCAN::next (this=0x7fffd40cadd0) at /home/psergey/dev-git2/10.5-look/sql/sql_join_cache.cc:3462
|
#6 0x0000555556080671 in JOIN_CACHE::join_matching_records (this=0x7fffd40cab90, skip_last=false) at /home/psergey/dev-git2/10.5-look/sql/sql_join_cache.cc:2338
|
#7 0x00005555560800b7 in JOIN_CACHE::join_records (this=0x7fffd40cab90, skip_last=false) at /home/psergey/dev-git2/10.5-look/sql/sql_join_cache.cc:2151
|
#8 0x0000555555f078c6 in sub_select_cache (join=0x7fffd401c3c0, join_tab=0x7fffd40c6748, end_of_records=true) at /home/psergey/dev-git2/10.5-look/sql/sql_select.cc:20690
|
#9 0x0000555555f07ad1 in sub_select (join=0x7fffd401c3c0, join_tab=0x7fffd40c63a0, end_of_records=true) at /home/psergey/dev-git2/10.5-look/sql/sql_select.cc:20861
|
#10 0x0000555555f078f2 in sub_select_cache (join=0x7fffd401c3c0, join_tab=0x7fffd40c63a0, end_of_records=true) at /home/psergey/dev-git2/10.5-look/sql/sql_select.cc:20693
|
#11 0x0000555555f07ad1 in sub_select (join=0x7fffd401c3c0, join_tab=0x7fffd40c5ff8, end_of_records=true) at /home/psergey/dev-git2/10.5-look/sql/sql_select.cc:20861
|
#12 0x0000555555f071d7 in do_select (join=0x7fffd401c3c0, procedure=0x0) at /home/psergey/dev-git2/10.5-look/sql/sql_select.cc:20450
|
#13 0x0000555555eda76c in JOIN::exec_inner (this=0x7fffd401c3c0) at /home/psergey/dev-git2/10.5-look/sql/sql_select.cc:4540
|
#14 0x0000555555ed9875 in JOIN::exec (this=0x7fffd401c3c0) at /home/psergey/dev-git2/10.5-look/sql/sql_select.cc:4320
|
#15 0x0000555555edb0ea in mysql_select (thd=0x7fffd4000d78, tables=0x7fffd40184c0, fields=..., conds=0x7fffd401a3e8, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fffd401c398, unit=0x7fffd4004f20, select_lex=0x7fffd4017bd0) at /home/psergey/dev-git2/10.5-look/sql/sql_select.cc:4797
|
#16 0x0000555555eca8a0 in handle_select (thd=0x7fffd4000d78, lex=0x7fffd4004e58, result=0x7fffd401c398, setup_tables_done_option=0) at /home/psergey/dev-git2/10.5-look/sql/sql_select.cc:444
|
#17 0x0000555555e8d058 in execute_sqlcom_select (thd=0x7fffd4000d78, all_tables=0x7fffd40184c0) at /home/psergey/dev-git2/10.5-look/sql/sql_parse.cc:6330
|
#18 0x0000555555e84279 in mysql_execute_command (thd=0x7fffd4000d78) at /home/psergey/dev-git2/10.5-look/sql/sql_parse.cc:4021
|
#19 0x0000555555e91e7d in mysql_parse (thd=0x7fffd4000d78, rawbuf=0x7fffd40178e0 "analyze format=json\nSELECT City.Name, Country.Name, CountryLanguage.Language\nFROM City,Country,CountryLanguage\nWHERE City.Country=Country.Code AND\nCountryLanguage.Country=Country.Code AND\nCity.Name LI"..., length=321, parser_state=0x7ffff0741510, is_com_multi=false, is_next_command=false) at /home/psergey/dev-git2/10.5-look/sql/sql_parse.cc:8116
|
#20 0x0000555555e7ddcc in dispatch_command (command=COM_QUERY, thd=0x7fffd4000d78, packet=0x7fffd400b589 "analyze format=json\nSELECT City.Name, Country.Name, CountryLanguage.Language\nFROM City,Country,CountryLanguage\nWHERE City.Country=Country.Code AND\nCountryLanguage.Country=Country.Code AND\nCity.Name LI"..., packet_length=321, is_com_multi=false, is_next_command=false) at /home/psergey/dev-git2/10.5-look/sql/sql_parse.cc:1907
|
#21 0x0000555555e7c46a in do_command (thd=0x7fffd4000d78) at /home/psergey/dev-git2/10.5-look/sql/sql_parse.cc:1375
|
#22 0x000055555602a767 in do_handle_one_connection (connect=0x5555589123e8, put_in_cache=true) at /home/psergey/dev-git2/10.5-look/sql/sql_connect.cc:1418
|
#23 0x000055555602a426 in handle_one_connection (arg=0x5555589123e8) at /home/psergey/dev-git2/10.5-look/sql/sql_connect.cc:1312
|
#24 0x000055555653c10b in pfs_spawn_thread (arg=0x555558848978) at /home/psergey/dev-git2/10.5-look/storage/perfschema/pfs.cc:2201
|
#25 0x00007ffff63346db in start_thread (arg=0x7ffff0742700) at pthread_create.c:463
|
#26 0x00007ffff551a61f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
|
(gdb) up
|
#1 0x0000555556a6353e in ha_myisam::rnd_next (this=0x7fffd402d540, buf=0x7fffd402d0e0 "\377AFGPashto", ' ' <repeats 24 times>, "\232\231QB", '\245' <repeats 42 times>, "\240\321\002\324\377\177") at /home/psergey/dev-git2/10.5-look/storage/myisam/ha_myisam.cc:2041
|
(gdb) p table->alias
|
$21 = {<Charset> = {m_charset = 0x5555578563e0 <my_charset_bin>}, <Binary_string> = {<Static_binary_string> = {<Sql_alloc> = {<No data fields>}, Ptr = 0x7fffd4078988 "CountryLanguage", str_length = 15}, Alloced_length = 16, extra_alloc = 0, alloced = true, thread_specific = false}, <No data fields>}
|
(gdb) p table->alias.Ptr
|
$23 = 0x7fffd4078988 "CountryLanguage"
|
Attachments
Issue Links
- relates to
-
-
- In Review
-