[MDEV-11287] Query that should result in only four rows never completes. Created: 2016-11-15  Updated: 2023-12-05

Status: Stalled
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.0.25, 5.5, 10.0, 10.1, 10.2
Fix Version/s: 10.2

Type: Bug Priority: Major
Reporter: Erik Berglund Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: None
Environment:

GNU/Linux Lubuntu 16.10, x86_64.

Attachments: File in_sql.sql     Text File queries.txt    
Issue Links:
Relates
relates to MDEV-19532 Query with subqueries hangs for long ... Closed

 Description   

Upon executing a series of SQL statements (see attached file) via JDBC, the server stops responding. The final statement should return just four rows, but does not terminate for at least 20 hours. After the last query all other operations on the server become impossible, and the server consumes 100% of CPU time on one logical processor, even after the query connection is terminated. The only remedy is to kill the server process query.

Executing the same statements directly in the CLI yields the same result.

The exact same sequence of SQL statements complete in a matter of seconds on a stock standard 5.7.16-0ubuntu0.16.10.1 MySQL server running on the same computer. Similar queries also complete in seconds on PostgreSQL, SQLite, HSQLDB, MonetDB, H2, Derby, and Firebird database engines running on the same computer.

The installed version is 10.0.25-MariaDB-1 Ubuntu 16.10, installed via the OS package manager and not configured further except to create a 'test' schema.

 Comments   
Comment by Elena Stepanova [ 2016-11-17 ]

I assume by "stock MySQL" you mean 5.7.
Same problem reproducible on MariaDB 5.5-10.2 and MySQL 5.5, 5.6.
Not reproducible on MySQL 5.7.

However, for me the query is easily killable.

It doesn't look like it's in a really indefinite loop, it just takes forever. A somewhat simplified query takes 1.5 hours, see more information below.

The query spends time in 'statistics' state:

+----+------+-----------------+------+---------+------+------------+------------------------------------------------------------------------------------------------------+----------+
 
| Id | User | Host            | db   | Command | Time | State      | Info                                                                                                 | Progress |
 
+----+------+-----------------+------+---------+------+------------+------------------------------------------------------------------------------------------------------+----------+
 
|  6 | root | localhost:58948 | test | Query   |   23 | statistics | SELECT * FROM JAVA_LANG_OBJECT AS V , JAVA_UTIL_SET AS Y , JAVA_UTIL_COLLECTION AS W , C__ARRAY AS B |    0.000 |

ANALYZE and EXPLAIN behave the same way.

Here are two consequent stack traces from ANALYZE:

10.1 bccd0b5e0e

 
Thread 2 (Thread 0x7f33e32e7b00 (LWP 26885)):
 
#0  0x00007f33e2445aad in TABLE_LIST::is_active_sjm (this=0x7f33b3224d18) at /data/src/10.1/sql/sql_select.cc:9442
 
#1  0x00007f33e24ea2a3 in TABLE::is_filled_at_execution (this=0x7f33b30f0870) at /data/src/10.1/sql/table.cc:6485
 
#2  0x00007f33e244af57 in st_join_table::scan_time (this=0x7f33b329c1c0) at /data/src/10.1/sql/sql_select.cc:11609
 
#3  0x00007f33e243e806 in best_access_path (join=0x7f33b3230af8, s=0x7f33b329c1c0, remaining_tables=6275073, idx=16, disable_jbuf=false, record_count=911250, pos=0x7f33b329f758, loose_scan_pos=0x7f33e32e2c80) at /data/src/10.1/sql/sql_select.cc:6393
 
#4  0x00007f33e2441820 in best_extension_by_limited_search (join=0x7f33b3230af8, remaining_tables=6275073, idx=16, record_count=911250, read_time=737948, search_depth=46, prune_level=1, use_cond_selectivity=1) at /data/src/10.1/sql/sql_select.cc:7864
 
#5  0x00007f33e2441d08 in best_extension_by_limited_search (join=0x7f33b3230af8, remaining_tables=6275137, idx=15, record_count=911250, read_time=555671, search_depth=47, prune_level=1, use_cond_selectivity=1) at /data/src/10.1/sql/sql_select.cc:7933
 
#6  0x00007f33e2441d08 in best_extension_by_limited_search (join=0x7f33b3230af8, remaining_tables=6275169, idx=14, record_count=911250, read_time=373394, search_depth=48, prune_level=1, use_cond_selectivity=1) at /data/src/10.1/sql/sql_select.cc:7933
 
#7  0x00007f33e2441d08 in best_extension_by_limited_search (join=0x7f33b3230af8, remaining_tables=14663777, idx=13, record_count=911250, read_time=191134, search_depth=49, prune_level=1, use_cond_selectivity=1) at /data/src/10.1/sql/sql_select.cc:7933
 
#8  0x00007f33e2441d08 in best_extension_by_limited_search (join=0x7f33b3230af8, remaining_tables=14663793, idx=12, record_count=33750, read_time=8858, search_depth=50, prune_level=1, use_cond_selectivity=1) at /data/src/10.1/sql/sql_select.cc:7933
 
#9  0x00007f33e2441d08 in best_extension_by_limited_search (join=0x7f33b3230af8, remaining_tables=16760945, idx=11, record_count=2250, read_time=2106, search_depth=51, prune_level=1, use_cond_selectivity=1) at /data/src/10.1/sql/sql_select.cc:7933
 
#10 0x00007f33e2441d08 in best_extension_by_limited_search (join=0x7f33b3230af8, remaining_tables=16762993, idx=10, record_count=2250, read_time=1641, search_depth=52, prune_level=1, use_cond_selectivity=1) at /data/src/10.1/sql/sql_select.cc:7933
 
#11 0x00007f33e2441d08 in best_extension_by_limited_search (join=0x7f33b3230af8, remaining_tables=16763001, idx=9, record_count=2250, read_time=1176, search_depth=53, prune_level=1, use_cond_selectivity=1) at /data/src/10.1/sql/sql_select.cc:7933
 
#12 0x00007f33e2441d08 in best_extension_by_limited_search (join=0x7f33b3230af8, remaining_tables=16763005, idx=8, record_count=2250, read_time=711, search_depth=54, prune_level=1, use_cond_selectivity=1) at /data/src/10.1/sql/sql_select.cc:7933
 
#13 0x00007f33e2441d08 in best_extension_by_limited_search (join=0x7f33b3230af8, remaining_tables=16763007, idx=7, record_count=150, read_time=260, search_depth=55, prune_level=1, use_cond_selectivity=1) at /data/src/10.1/sql/sql_select.cc:7933
 
#14 0x00007f33e2441d08 in best_extension_by_limited_search (join=0x7f33b3230af8, remaining_tables=16771199, idx=6, record_count=150, read_time=215, search_depth=56, prune_level=1, use_cond_selectivity=1) at /data/src/10.1/sql/sql_select.cc:7933
 
#15 0x00007f33e2441d08 in best_extension_by_limited_search (join=0x7f33b3230af8, remaining_tables=16775295, idx=5, record_count=150, read_time=170, search_depth=57, prune_level=1, use_cond_selectivity=1) at /data/src/10.1/sql/sql_select.cc:7933
 
#16 0x00007f33e2441d08 in best_extension_by_limited_search (join=0x7f33b3230af8, remaining_tables=16776319, idx=4, record_count=150, read_time=125, search_depth=58, prune_level=1, use_cond_selectivity=1) at /data/src/10.1/sql/sql_select.cc:7933
 
#17 0x00007f33e2441d08 in best_extension_by_limited_search (join=0x7f33b3230af8, remaining_tables=16776831, idx=3, record_count=150, read_time=80, search_depth=59, prune_level=1, use_cond_selectivity=1) at /data/src/10.1/sql/sql_select.cc:7933
 
#18 0x00007f33e2441d08 in best_extension_by_limited_search (join=0x7f33b3230af8, remaining_tables=16776959, idx=2, record_count=150, read_time=35, search_depth=60, prune_level=1, use_cond_selectivity=1) at /data/src/10.1/sql/sql_select.cc:7933
 
#19 0x00007f33e2441d08 in best_extension_by_limited_search (join=0x7f33b3230af8, remaining_tables=33554175, idx=1, record_count=15, read_time=4, search_depth=61, prune_level=1, use_cond_selectivity=1) at /data/src/10.1/sql/sql_select.cc:7933
 
#20 0x00007f33e2441d08 in best_extension_by_limited_search (join=0x7f33b3230af8, remaining_tables=33554431, idx=0, record_count=1, read_time=0, search_depth=62, prune_level=1, use_cond_selectivity=1) at /data/src/10.1/sql/sql_select.cc:7933
 
#21 0x00007f33e243fdcd in greedy_search (join=0x7f33b3230af8, remaining_tables=33554431, search_depth=62, prune_level=1, use_cond_selectivity=1) at /data/src/10.1/sql/sql_select.cc:7098
 
#22 0x00007f33e243f307 in choose_plan (join=0x7f33b3230af8, join_tables=33554431) at /data/src/10.1/sql/sql_select.cc:6675
 
#23 0x00007f33e2438718 in make_join_statistics (join=0x7f33b3230af8, tables_list=..., keyuse_array=0x7f33b3230e28) at /data/src/10.1/sql/sql_select.cc:4186
 
#24 0x00007f33e242e511 in JOIN::optimize_inner (this=0x7f33b3230af8) at /data/src/10.1/sql/sql_select.cc:1361
 
#25 0x00007f33e242d3fc in JOIN::optimize (this=0x7f33b3230af8) at /data/src/10.1/sql/sql_select.cc:1040
 
#26 0x00007f33e2435a38 in mysql_select (thd=0x7f33bba87070, rref_pointer_array=0x7f33bba8b430, tables=0x7f33b3022d58, wild_num=1, fields=..., conds=0x7f33b322a3a8, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f33b3230ad8, unit=0x7f33bba8aab8, select_lex=0x7f33bba8b1b8) at /data/src/10.1/sql/sql_select.cc:3424
 
#27 0x00007f33e242b5bc in handle_select (thd=0x7f33bba87070, lex=0x7f33bba8a9f0, result=0x7f33b3230ad8, setup_tables_done_option=0) at /data/src/10.1/sql/sql_select.cc:384
 
#28 0x00007f33e23fb793 in execute_sqlcom_select (thd=0x7f33bba87070, all_tables=0x7f33b3022d58) at /data/src/10.1/sql/sql_parse.cc:5893
 
#29 0x00007f33e23f16b5 in mysql_execute_command (thd=0x7f33bba87070) at /data/src/10.1/sql/sql_parse.cc:2965
 
#30 0x00007f33e23feeec in mysql_parse (thd=0x7f33bba87070, rawbuf=0x7f33b3022088 "ANALYZE SELECT * FROM JAVA_LANG_OBJECT AS V , JAVA_UTIL_SET AS Y , JAVA_UTIL_COLLECTION AS W , C__ARRAY AS BB , C__ARRAY_MEMBER_JAVA_LANG_OBJECT AS FB , JAVA_LANG_OBJECT AS HB , ORG_CONSERVE_OBJECTS_B"..., length=1409, parser_state=0x7f33e32e6630) at /data/src/10.1/sql/sql_parse.cc:7316
 
#31 0x00007f33e23ed8d9 in dispatch_command (command=COM_QUERY, thd=0x7f33bba87070, packet=0x7f33bbfe2071 "ANALYZE SELECT * FROM JAVA_LANG_OBJECT AS V , JAVA_UTIL_SET AS Y , JAVA_UTIL_COLLECTION AS W , C__ARRAY AS BB , C__ARRAY_MEMBER_JAVA_LANG_OBJECT AS FB , JAVA_LANG_OBJECT AS HB , ORG_CONSERVE_OBJECTS_B"..., packet_length=1409) at /data/src/10.1/sql/sql_parse.cc:1487
 
#32 0x00007f33e23ec610 in do_command (thd=0x7f33bba87070) at /data/src/10.1/sql/sql_parse.cc:1108
 
#33 0x00007f33e25226db in do_handle_one_connection (thd_arg=0x7f33bba87070) at /data/src/10.1/sql/sql_connect.cc:1350
 
#34 0x00007f33e252243f in handle_one_connection (arg=0x7f33bba87070) at /data/src/10.1/sql/sql_connect.cc:1262
 
#35 0x00007f33e1aea0a4 in start_thread (arg=0x7f33e32e7b00) at pthread_create.c:309
 
#36 0x00007f33dfca287d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111


Thread 2 (Thread 0x7f33e32e7b00 (LWP 26885)):
 
#0  st_join_table::scan_time (this=0x7f33b329dc00) at /data/src/10.1/sql/sql_select.cc:11618
 
#1  0x00007f33e243e806 in best_access_path (join=0x7f33b3230af8, s=0x7f33b329dc00, remaining_tables=12599315, idx=19, disable_jbuf=false, record_count=3037500, pos=0x7f33b329fa70, loose_scan_pos=0x7f33e32e2650) at /data/src/10.1/sql/sql_select.cc:6393
 
#2  0x00007f33e2441820 in best_extension_by_limited_search (join=0x7f33b3230af8, remaining_tables=12599315, idx=19, record_count=3037500, read_time=1346514, search_depth=43, prune_level=1, use_cond_selectivity=1) at /data/src/10.1/sql/sql_select.cc:7864
 
#3  0x00007f33e2441d08 in best_extension_by_limited_search (join=0x7f33b3230af8, remaining_tables=12861459, idx=18, record_count=3037500, read_time=739008, search_depth=44, prune_level=1, use_cond_selectivity=1) at /data/src/10.1/sql/sql_select.cc:7933
 
#4  0x00007f33e2441d08 in best_extension_by_limited_search (join=0x7f33b3230af8, remaining_tables=12861463, idx=17, record_count=202500, read_time=131280, search_depth=45, prune_level=1, use_cond_selectivity=1) at /data/src/10.1/sql/sql_select.cc:7933
 
#5  0x00007f33e2441d08 in best_extension_by_limited_search (join=0x7f33b3230af8, remaining_tables=12861495, idx=16, record_count=202500, read_time=90774, search_depth=46, prune_level=1, use_cond_selectivity=1) at /data/src/10.1/sql/sql_select.cc:7933
 
#6  0x00007f33e2441d08 in best_extension_by_limited_search (join=0x7f33b3230af8, remaining_tables=14958647, idx=15, record_count=202500, read_time=50259, search_depth=47, prune_level=1, use_cond_selectivity=1) at /data/src/10.1/sql/sql_select.cc:7933
 
#7  0x00007f33e2441d08 in best_extension_by_limited_search (join=0x7f33b3230af8, remaining_tables=14958655, idx=14, record_count=13500, read_time=9746, search_depth=48, prune_level=1, use_cond_selectivity=1) at /data/src/10.1/sql/sql_select.cc:7933
 
#8  0x00007f33e2441d08 in best_extension_by_limited_search (join=0x7f33b3230af8, remaining_tables=15482943, idx=13, record_count=13500, read_time=7040, search_depth=49, prune_level=1, use_cond_selectivity=1) at /data/src/10.1/sql/sql_select.cc:7933
 
#9  0x00007f33e2441d08 in best_extension_by_limited_search (join=0x7f33b3230af8, remaining_tables=15614015, idx=12, record_count=13500, read_time=4334, search_depth=50, prune_level=1, use_cond_selectivity=1) at /data/src/10.1/sql/sql_select.cc:7933
 
#10 0x00007f33e2441d08 in best_extension_by_limited_search (join=0x7f33b3230af8, remaining_tables=15614079, idx=11, record_count=2250, read_time=1632, search_depth=51, prune_level=1, use_cond_selectivity=1) at /data/src/10.1/sql/sql_select.cc:7933
 
#11 0x00007f33e2441d08 in best_extension_by_limited_search (join=0x7f33b3230af8, remaining_tables=15679615, idx=10, record_count=2250, read_time=1167, search_depth=52, prune_level=1, use_cond_selectivity=1) at /data/src/10.1/sql/sql_select.cc:7933
 
#12 0x00007f33e2441d08 in best_extension_by_limited_search (join=0x7f33b3230af8, remaining_tables=15712383, idx=9, record_count=2250, read_time=702, search_depth=53, prune_level=1, use_cond_selectivity=1) at /data/src/10.1/sql/sql_select.cc:7933
 
#13 0x00007f33e2441d08 in best_extension_by_limited_search (join=0x7f33b3230af8, remaining_tables=16760959, idx=8, record_count=150, read_time=251, search_depth=54, prune_level=1, use_cond_selectivity=1) at /data/src/10.1/sql/sql_select.cc:7933
 
#14 0x00007f33e2441d08 in best_extension_by_limited_search (join=0x7f33b3230af8, remaining_tables=16763007, idx=7, record_count=150, read_time=206, search_depth=55, prune_level=1, use_cond_selectivity=1) at /data/src/10.1/sql/sql_select.cc:7933
 
#15 0x00007f33e2441d08 in best_extension_by_limited_search (join=0x7f33b3230af8, remaining_tables=16771199, idx=6, record_count=150, read_time=161, search_depth=56, prune_level=1, use_cond_selectivity=1) at /data/src/10.1/sql/sql_select.cc:7933
 
#16 0x00007f33e2441d08 in best_extension_by_limited_search (join=0x7f33b3230af8, remaining_tables=16775295, idx=5, record_count=150, read_time=116, search_depth=57, prune_level=1, use_cond_selectivity=1) at /data/src/10.1/sql/sql_select.cc:7933
 
#17 0x00007f33e2441d08 in best_extension_by_limited_search (join=0x7f33b3230af8, remaining_tables=16776319, idx=4, record_count=150, read_time=71, search_depth=58, prune_level=1, use_cond_selectivity=1) at /data/src/10.1/sql/sql_select.cc:7933
 
#18 0x00007f33e2441d08 in best_extension_by_limited_search (join=0x7f33b3230af8, remaining_tables=33553535, idx=3, record_count=15, read_time=40, search_depth=59, prune_level=1, use_cond_selectivity=1) at /data/src/10.1/sql/sql_select.cc:7933
 
#19 0x00007f33e2441d08 in best_extension_by_limited_search (join=0x7f33b3230af8, remaining_tables=33554047, idx=2, record_count=15, read_time=22, search_depth=60, prune_level=1, use_cond_selectivity=1) at /data/src/10.1/sql/sql_select.cc:7933
 
#20 0x00007f33e2441d08 in best_extension_by_limited_search (join=0x7f33b3230af8, remaining_tables=33554175, idx=1, record_count=15, read_time=4, search_depth=61, prune_level=1, use_cond_selectivity=1) at /data/src/10.1/sql/sql_select.cc:7933
 
#21 0x00007f33e2441d08 in best_extension_by_limited_search (join=0x7f33b3230af8, remaining_tables=33554431, idx=0, record_count=1, read_time=0, search_depth=62, prune_level=1, use_cond_selectivity=1) at /data/src/10.1/sql/sql_select.cc:7933
 
#22 0x00007f33e243fdcd in greedy_search (join=0x7f33b3230af8, remaining_tables=33554431, search_depth=62, prune_level=1, use_cond_selectivity=1) at /data/src/10.1/sql/sql_select.cc:7098
 
#23 0x00007f33e243f307 in choose_plan (join=0x7f33b3230af8, join_tables=33554431) at /data/src/10.1/sql/sql_select.cc:6675
 
#24 0x00007f33e2438718 in make_join_statistics (join=0x7f33b3230af8, tables_list=..., keyuse_array=0x7f33b3230e28) at /data/src/10.1/sql/sql_select.cc:4186
 
#25 0x00007f33e242e511 in JOIN::optimize_inner (this=0x7f33b3230af8) at /data/src/10.1/sql/sql_select.cc:1361
 
#26 0x00007f33e242d3fc in JOIN::optimize (this=0x7f33b3230af8) at /data/src/10.1/sql/sql_select.cc:1040
 
#27 0x00007f33e2435a38 in mysql_select (thd=0x7f33bba87070, rref_pointer_array=0x7f33bba8b430, tables=0x7f33b3022d58, wild_num=1, fields=..., conds=0x7f33b322a3a8, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f33b3230ad8, unit=0x7f33bba8aab8, select_lex=0x7f33bba8b1b8) at /data/src/10.1/sql/sql_select.cc:3424
 
#28 0x00007f33e242b5bc in handle_select (thd=0x7f33bba87070, lex=0x7f33bba8a9f0, result=0x7f33b3230ad8, setup_tables_done_option=0) at /data/src/10.1/sql/sql_select.cc:384
 
#29 0x00007f33e23fb793 in execute_sqlcom_select (thd=0x7f33bba87070, all_tables=0x7f33b3022d58) at /data/src/10.1/sql/sql_parse.cc:5893
 
#30 0x00007f33e23f16b5 in mysql_execute_command (thd=0x7f33bba87070) at /data/src/10.1/sql/sql_parse.cc:2965
 
#31 0x00007f33e23feeec in mysql_parse (thd=0x7f33bba87070, rawbuf=0x7f33b3022088 "ANALYZE SELECT * FROM JAVA_LANG_OBJECT AS V , JAVA_UTIL_SET AS Y , JAVA_UTIL_COLLECTION AS W , C__ARRAY AS BB , C__ARRAY_MEMBER_JAVA_LANG_OBJECT AS FB , JAVA_LANG_OBJECT AS HB , ORG_CONSERVE_OBJECTS_B"..., length=1409, parser_state=0x7f33e32e6630) at /data/src/10.1/sql/sql_parse.cc:7316
 
#32 0x00007f33e23ed8d9 in dispatch_command (command=COM_QUERY, thd=0x7f33bba87070, packet=0x7f33bbfe2071 "ANALYZE SELECT * FROM JAVA_LANG_OBJECT AS V , JAVA_UTIL_SET AS Y , JAVA_UTIL_COLLECTION AS W , C__ARRAY AS BB , C__ARRAY_MEMBER_JAVA_LANG_OBJECT AS FB , JAVA_LANG_OBJECT AS HB , ORG_CONSERVE_OBJECTS_B"..., packet_length=1409) at /data/src/10.1/sql/sql_parse.cc:1487
 
#33 0x00007f33e23ec610 in do_command (thd=0x7f33bba87070) at /data/src/10.1/sql/sql_parse.cc:1108
 
#34 0x00007f33e25226db in do_handle_one_connection (thd_arg=0x7f33bba87070) at /data/src/10.1/sql/sql_connect.cc:1350
 
#35 0x00007f33e252243f in handle_one_connection (arg=0x7f33bba87070) at /data/src/10.1/sql/sql_connect.cc:1262
 
#36 0x00007f33e1aea0a4 in start_thread (arg=0x7f33e32e7b00) at pthread_create.c:309
 
#37 0x00007f33dfca287d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111

This is the result for the simplified query which takes ~1.5 hours (1 hour 26 min 42.75 sec):

MariaDB [test]> ANALYZE SELECT * FROM JAVA_LANG_OBJECT AS V , JAVA_UTIL_SET AS Y , JAVA_UTIL_COLLECTION AS W , C__ARRAY AS BB , 
C__ARRAY_MEMBER_JAVA_LANG_OBJECT AS FB , JAVA_LANG_OBJECT AS HB , ORG_CONSERVE_OBJECTS_BOOK AS GB , JAVA_UTIL_SET AS OB , 
JAVA_UTIL_HASHSET AS IB , JAVA_UTIL_ABSTRACTSET AS JB , JAVA_UTIL_ABSTRACTCOLLECTION AS KB , JAVA_LANG_OBJECT AS LB , 
JAVA_UTIL_COLLECTION AS MB , C__ARRAY AS RB , C__ARRAY_MEMBER_JAVA_LANG_OBJECT AS VB , JAVA_UTIL_HASHSET AS WB , 
JAVA_UTIL_ABSTRACTSET AS XB , JAVA_UTIL_ABSTRACTCOLLECTION AS YB , JAVA_LANG_OBJECT AS ZB , JAVA_UTIL_SET AS CC , 
JAVA_UTIL_COLLECTION AS AC , C__ARRAY AS FC , C__ARRAY_MEMBER_JAVA_LANG_OBJECT AS JC , JAVA_LANG_OBJECT AS LC , JAVA_LANG_STRING AS KC 
WHERE Y.C__ID = W.C__ID AND GB.C__ID = HB.C__ID AND IB.C__ID = JB.C__ID AND JB.C__ID = KB.C__ID AND KB.C__ID = LB.C__ID 
AND JB.C__ID = OB.C__ID AND OB.C__ID = MB.C__ID AND WB.C__ID = XB.C__ID AND XB.C__ID = YB.C__ID AND YB.C__ID = ZB.C__ID 
AND XB.C__ID = CC.C__ID AND CC.C__ID = AC.C__ID AND KC.C__ID = LC.C__ID AND W.EMPTY_ = 0 AND W.C__COLLECTION_CONTENTS = BB.C__ID 
AND BB.C__ID = FB.C__ARRAY_MEMBER_ID AND FB.C__VALUE  = HB.C__ID AND GB.AUTHORS = OB.C__ID AND MB.EMPTY_ = 0 
AND MB.C__COLLECTION_CONTENTS = RB.C__ID AND GB.KEYWORDS = WB.C__ID AND AC.EMPTY_ = 0 AND AC.C__COLLECTION_CONTENTS = FC.C__ID 
AND FC.C__ID = JC.C__ARRAY_MEMBER_ID AND JC.C__VALUE  = LC.C__ID AND KC.C__VALUE = 'crime';


+------+-------------+-------+--------+------------------------+---------+---------+--------------------------------+------+--------+----------+------------+-------------------------------------------------+
 
| id   | select_type | table | type   | possible_keys          | key     | key_len | ref                            | rows | r_rows | filtered | r_filtered | Extra                                           |
 
+------+-------------+-------+--------+------------------------+---------+---------+--------------------------------+------+--------+----------+------------+-------------------------------------------------+
 
|    1 | SIMPLE      | Y     | ALL    | PRIMARY                | NULL    | NULL    | NULL                           |   15 |  15.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | W     | eq_ref | PRIMARY                | PRIMARY | 8       | test.Y.C__ID                   |    1 |   1.00 |   100.00 |     100.00 | Using where                                     |
 
|    1 | SIMPLE      | BB    | eq_ref | PRIMARY,C__ARRAY_INDEX | PRIMARY | 8       | test.W.C__COLLECTION_CONTENTS  |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | FB    | ALL    | NULL                   | NULL    | NULL    | NULL                           |   27 |  27.00 |   100.00 |       6.67 | Using where; Using join buffer (flat, BNL join) |
 
|    1 | SIMPLE      | GB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.FB.C__VALUE               |    1 |   0.22 |   100.00 |     100.00 | Using where                                     |
 
|    1 | SIMPLE      | OB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.AUTHORS                |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | IB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.AUTHORS                |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | JB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.AUTHORS                |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | KB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.AUTHORS                |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | MB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.AUTHORS                |    1 |   1.00 |   100.00 |     100.00 | Using where                                     |
 
|    1 | SIMPLE      | RB    | eq_ref | PRIMARY,C__ARRAY_INDEX | PRIMARY | 8       | test.MB.C__COLLECTION_CONTENTS |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | WB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.KEYWORDS               |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | XB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.KEYWORDS               |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | YB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.KEYWORDS               |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | CC    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.KEYWORDS               |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | AC    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.KEYWORDS               |    1 |   1.00 |   100.00 |     100.00 | Using where                                     |
 
|    1 | SIMPLE      | FC    | eq_ref | PRIMARY,C__ARRAY_INDEX | PRIMARY | 8       | test.AC.C__COLLECTION_CONTENTS |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | HB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.FB.C__VALUE               |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | LB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.AUTHORS                |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | JC    | ALL    | NULL                   | NULL    | NULL    | NULL                           |   27 |  27.00 |   100.00 |       9.26 | Using where; Using join buffer (flat, BNL join) |
 
|    1 | SIMPLE      | KC    | eq_ref | PRIMARY                | PRIMARY | 8       | test.JC.C__VALUE               |    1 |   1.00 |   100.00 |      13.33 | Using where                                     |
 
|    1 | SIMPLE      | ZB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.KEYWORDS               |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | LC    | eq_ref | PRIMARY                | PRIMARY | 8       | test.JC.C__VALUE               |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | VB    | ALL    | NULL                   | NULL    | NULL    | NULL                           |   27 |  27.00 |   100.00 |     100.00 | Using join buffer (flat, BNL join)              |
 
|    1 | SIMPLE      | V     | ALL    | NULL                   | NULL    | NULL    | NULL                           |   49 |  49.00 |   100.00 |     100.00 | Using join buffer (incremental, BNL join)       |
 
+------+-------------+-------+--------+------------------------+---------+---------+--------------------------------+------+--------+----------+------------+-------------------------------------------------+
 
25 rows in set (1 hour 26 min 42.75 sec)

If we remove just one more table, it gets much faster (11.14 sec):

MariaDB [test]> ANALYZE SELECT * FROM  JAVA_UTIL_SET AS Y , JAVA_UTIL_COLLECTION AS W , C__ARRAY AS BB , 
C__ARRAY_MEMBER_JAVA_LANG_OBJECT AS FB , JAVA_LANG_OBJECT AS HB , ORG_CONSERVE_OBJECTS_BOOK AS GB , JAVA_UTIL_SET AS OB , 
JAVA_UTIL_HASHSET AS IB , JAVA_UTIL_ABSTRACTSET AS JB , JAVA_UTIL_ABSTRACTCOLLECTION AS KB , JAVA_LANG_OBJECT AS LB , 
JAVA_UTIL_COLLECTION AS MB , C__ARRAY AS RB , C__ARRAY_MEMBER_JAVA_LANG_OBJECT AS VB , JAVA_UTIL_HASHSET AS WB , 
JAVA_UTIL_ABSTRACTSET AS XB , JAVA_UTIL_ABSTRACTCOLLECTION AS YB , JAVA_LANG_OBJECT AS ZB , JAVA_UTIL_SET AS CC , 
JAVA_UTIL_COLLECTION AS AC , C__ARRAY AS FC , C__ARRAY_MEMBER_JAVA_LANG_OBJECT AS JC , JAVA_LANG_OBJECT AS LC , JAVA_LANG_STRING AS KC 
WHERE GB.C__ID = HB.C__ID AND IB.C__ID = JB.C__ID AND JB.C__ID = KB.C__ID AND KB.C__ID = LB.C__ID AND JB.C__ID = OB.C__ID 
AND OB.C__ID = MB.C__ID AND WB.C__ID = XB.C__ID AND XB.C__ID = YB.C__ID AND YB.C__ID = ZB.C__ID AND XB.C__ID = CC.C__ID 
AND CC.C__ID = AC.C__ID AND KC.C__ID = LC.C__ID AND W.EMPTY_ = 0 AND W.C__COLLECTION_CONTENTS = BB.C__ID 
AND BB.C__ID = FB.C__ARRAY_MEMBER_ID AND FB.C__VALUE  = HB.C__ID AND GB.AUTHORS = OB.C__ID AND MB.EMPTY_ = 0 
AND MB.C__COLLECTION_CONTENTS = RB.C__ID AND GB.KEYWORDS = WB.C__ID AND AC.EMPTY_ = 0 AND AC.C__COLLECTION_CONTENTS = FC.C__ID 
AND FC.C__ID = JC.C__ARRAY_MEMBER_ID AND JC.C__VALUE  = LC.C__ID AND KC.C__VALUE = 'crime';


+------+-------------+-------+--------+------------------------+---------+---------+--------------------------------+------+--------+----------+------------+-------------------------------------------------+
 
| id   | select_type | table | type   | possible_keys          | key     | key_len | ref                            | rows | r_rows | filtered | r_filtered | Extra                                           |
 
+------+-------------+-------+--------+------------------------+---------+---------+--------------------------------+------+--------+----------+------------+-------------------------------------------------+
 
|    1 | SIMPLE      | Y     | ALL    | NULL                   | NULL    | NULL    | NULL                           |   15 |  15.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | W     | ALL    | NULL                   | NULL    | NULL    | NULL                           |   15 |  15.00 |   100.00 |     100.00 | Using where; Using join buffer (flat, BNL join) |
 
|    1 | SIMPLE      | BB    | eq_ref | PRIMARY,C__ARRAY_INDEX | PRIMARY | 8       | test.W.C__COLLECTION_CONTENTS  |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | FB    | ALL    | NULL                   | NULL    | NULL    | NULL                           |   27 |  27.00 |   100.00 |       6.67 | Using where; Using join buffer (flat, BNL join) |
 
|    1 | SIMPLE      | GB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.FB.C__VALUE               |    1 |   0.22 |   100.00 |     100.00 | Using where                                     |
 
|    1 | SIMPLE      | OB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.AUTHORS                |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | IB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.AUTHORS                |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | JB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.AUTHORS                |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | KB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.AUTHORS                |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | MB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.AUTHORS                |    1 |   1.00 |   100.00 |     100.00 | Using where                                     |
 
|    1 | SIMPLE      | RB    | eq_ref | PRIMARY,C__ARRAY_INDEX | PRIMARY | 8       | test.MB.C__COLLECTION_CONTENTS |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | WB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.KEYWORDS               |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | XB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.KEYWORDS               |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | YB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.KEYWORDS               |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | CC    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.KEYWORDS               |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | AC    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.KEYWORDS               |    1 |   1.00 |   100.00 |     100.00 | Using where                                     |
 
|    1 | SIMPLE      | FC    | eq_ref | PRIMARY,C__ARRAY_INDEX | PRIMARY | 8       | test.AC.C__COLLECTION_CONTENTS |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | HB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.FB.C__VALUE               |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | LB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.AUTHORS                |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | JC    | ALL    | NULL                   | NULL    | NULL    | NULL                           |   27 |  27.00 |   100.00 |       9.26 | Using where; Using join buffer (flat, BNL join) |
 
|    1 | SIMPLE      | KC    | eq_ref | PRIMARY                | PRIMARY | 8       | test.JC.C__VALUE               |    1 |   1.00 |   100.00 |      13.33 | Using where                                     |
 
|    1 | SIMPLE      | ZB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.KEYWORDS               |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | LC    | eq_ref | PRIMARY                | PRIMARY | 8       | test.JC.C__VALUE               |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | VB    | ALL    | NULL                   | NULL    | NULL    | NULL                           |   27 |  27.00 |   100.00 |     100.00 | Using join buffer (flat, BNL join)              |
 
+------+-------------+-------+--------+------------------------+---------+---------+--------------------------------+------+--------+----------+------------+-------------------------------------------------+
 
24 rows in set (11.14 sec)

Removing one more table doesn't change things much (11.91 sec):

MariaDB [test]> ANALYZE SELECT * FROM JAVA_UTIL_COLLECTION AS W , C__ARRAY AS BB , C__ARRAY_MEMBER_JAVA_LANG_OBJECT AS FB , 
JAVA_LANG_OBJECT AS HB , ORG_CONSERVE_OBJECTS_BOOK AS GB , JAVA_UTIL_SET AS OB , JAVA_UTIL_HASHSET AS IB , JAVA_UTIL_ABSTRACTSET AS JB, 
JAVA_UTIL_ABSTRACTCOLLECTION AS KB , JAVA_LANG_OBJECT AS LB , JAVA_UTIL_COLLECTION AS MB , C__ARRAY AS RB , 
C__ARRAY_MEMBER_JAVA_LANG_OBJECT AS VB , JAVA_UTIL_HASHSET AS WB , JAVA_UTIL_ABSTRACTSET AS XB , JAVA_UTIL_ABSTRACTCOLLECTION AS YB , 
JAVA_LANG_OBJECT AS ZB , JAVA_UTIL_SET AS CC , JAVA_UTIL_COLLECTION AS AC , C__ARRAY AS FC , C__ARRAY_MEMBER_JAVA_LANG_OBJECT AS JC , 
JAVA_LANG_OBJECT AS LC , JAVA_LANG_STRING AS KC 
WHERE GB.C__ID = HB.C__ID AND IB.C__ID = JB.C__ID AND JB.C__ID = KB.C__ID AND KB.C__ID = LB.C__ID AND JB.C__ID = OB.C__ID 
AND OB.C__ID = MB.C__ID AND WB.C__ID = XB.C__ID AND XB.C__ID = YB.C__ID AND YB.C__ID = ZB.C__ID AND XB.C__ID = CC.C__ID 
AND CC.C__ID = AC.C__ID AND KC.C__ID = LC.C__ID AND BB.C__ID = FB.C__ARRAY_MEMBER_ID AND FB.C__VALUE  = HB.C__ID 
AND GB.AUTHORS = OB.C__ID AND MB.EMPTY_ = 0 AND MB.C__COLLECTION_CONTENTS = RB.C__ID AND GB.KEYWORDS = WB.C__ID AND AC.EMPTY_ = 0 
AND AC.C__COLLECTION_CONTENTS = FC.C__ID AND FC.C__ID = JC.C__ARRAY_MEMBER_ID AND JC.C__VALUE  = LC.C__ID AND KC.C__VALUE = 'crime';


+------+-------------+-------+--------+------------------------+---------+---------+--------------------------------+------+--------+----------+------------+-------------------------------------------------+
 
| id   | select_type | table | type   | possible_keys          | key     | key_len | ref                            | rows | r_rows | filtered | r_filtered | Extra                                           |
 
+------+-------------+-------+--------+------------------------+---------+---------+--------------------------------+------+--------+----------+------------+-------------------------------------------------+
 
|    1 | SIMPLE      | W     | ALL    | NULL                   | NULL    | NULL    | NULL                           |   15 |  15.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | FB    | ALL    | NULL                   | NULL    | NULL    | NULL                           |   27 |  27.00 |   100.00 |     100.00 | Using where; Using join buffer (flat, BNL join) |
 
|    1 | SIMPLE      | GB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.FB.C__VALUE               |    1 |   0.22 |   100.00 |     100.00 | Using where                                     |
 
|    1 | SIMPLE      | BB    | eq_ref | PRIMARY,C__ARRAY_INDEX | PRIMARY | 8       | test.FB.C__ARRAY_MEMBER_ID     |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | OB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.AUTHORS                |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | IB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.AUTHORS                |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | JB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.AUTHORS                |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | KB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.AUTHORS                |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | MB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.AUTHORS                |    1 |   1.00 |   100.00 |     100.00 | Using where                                     |
 
|    1 | SIMPLE      | RB    | eq_ref | PRIMARY,C__ARRAY_INDEX | PRIMARY | 8       | test.MB.C__COLLECTION_CONTENTS |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | WB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.KEYWORDS               |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | XB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.KEYWORDS               |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | YB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.KEYWORDS               |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | CC    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.KEYWORDS               |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | AC    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.KEYWORDS               |    1 |   1.00 |   100.00 |     100.00 | Using where                                     |
 
|    1 | SIMPLE      | FC    | eq_ref | PRIMARY,C__ARRAY_INDEX | PRIMARY | 8       | test.AC.C__COLLECTION_CONTENTS |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | HB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.FB.C__VALUE               |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | LB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.AUTHORS                |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | JC    | ALL    | NULL                   | NULL    | NULL    | NULL                           |   27 |  27.00 |   100.00 |       9.26 | Using where; Using join buffer (flat, BNL join) |
 
|    1 | SIMPLE      | KC    | eq_ref | PRIMARY                | PRIMARY | 8       | test.JC.C__VALUE               |    1 |   1.00 |   100.00 |      13.33 | Using where                                     |
 
|    1 | SIMPLE      | ZB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.KEYWORDS               |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | LC    | eq_ref | PRIMARY                | PRIMARY | 8       | test.JC.C__VALUE               |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | VB    | ALL    | NULL                   | NULL    | NULL    | NULL                           |   27 |  27.00 |   100.00 |     100.00 | Using join buffer (flat, BNL join)              |
 
+------+-------------+-------+--------+------------------------+---------+---------+--------------------------------+------+--------+----------+------------+-------------------------------------------------+
 
23 rows in set (11.91 sec)

One more table, it's faster again (5.40 sec):

MariaDB [test]> ANALYZE SELECT * FROM C__ARRAY AS BB , C__ARRAY_MEMBER_JAVA_LANG_OBJECT AS FB , JAVA_LANG_OBJECT AS HB , 
ORG_CONSERVE_OBJECTS_BOOK AS GB , JAVA_UTIL_SET AS OB , JAVA_UTIL_HASHSET AS IB , JAVA_UTIL_ABSTRACTSET AS JB , 
JAVA_UTIL_ABSTRACTCOLLECTION AS KB , JAVA_LANG_OBJECT AS LB , JAVA_UTIL_COLLECTION AS MB , C__ARRAY AS RB , 
C__ARRAY_MEMBER_JAVA_LANG_OBJECT AS VB , JAVA_UTIL_HASHSET AS WB , JAVA_UTIL_ABSTRACTSET AS XB , JAVA_UTIL_ABSTRACTCOLLECTION AS YB , 
JAVA_LANG_OBJECT AS ZB , JAVA_UTIL_SET AS CC , JAVA_UTIL_COLLECTION AS AC , C__ARRAY AS FC , C__ARRAY_MEMBER_JAVA_LANG_OBJECT AS JC , 
JAVA_LANG_OBJECT AS LC , JAVA_LANG_STRING AS KC 
WHERE GB.C__ID = HB.C__ID AND IB.C__ID = JB.C__ID AND JB.C__ID = KB.C__ID AND KB.C__ID = LB.C__ID AND JB.C__ID = OB.C__ID 
AND OB.C__ID = MB.C__ID AND WB.C__ID = XB.C__ID AND XB.C__ID = YB.C__ID AND YB.C__ID = ZB.C__ID AND XB.C__ID = CC.C__ID 
AND CC.C__ID = AC.C__ID AND KC.C__ID = LC.C__ID AND FB.C__VALUE  = HB.C__ID AND GB.AUTHORS = OB.C__ID AND MB.EMPTY_ = 0 
AND MB.C__COLLECTION_CONTENTS = RB.C__ID AND GB.KEYWORDS = WB.C__ID AND AC.EMPTY_ = 0 AND AC.C__COLLECTION_CONTENTS = FC.C__ID 
AND FC.C__ID = JC.C__ARRAY_MEMBER_ID AND JC.C__VALUE  = LC.C__ID AND KC.C__VALUE = 'crime';


+------+-------------+-------+--------+------------------------+---------+---------+--------------------------------+------+--------+----------+------------+-------------------------------------------------+
 
| id   | select_type | table | type   | possible_keys          | key     | key_len | ref                            | rows | r_rows | filtered | r_filtered | Extra                                           |
 
+------+-------------+-------+--------+------------------------+---------+---------+--------------------------------+------+--------+----------+------------+-------------------------------------------------+
 
|    1 | SIMPLE      | BB    | ALL    | NULL                   | NULL    | NULL    | NULL                           |   15 |  15.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | FB    | ALL    | NULL                   | NULL    | NULL    | NULL                           |   27 |  27.00 |   100.00 |     100.00 | Using where; Using join buffer (flat, BNL join) |
 
|    1 | SIMPLE      | GB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.FB.C__VALUE               |    1 |   0.22 |   100.00 |     100.00 | Using where                                     |
 
|    1 | SIMPLE      | OB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.AUTHORS                |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | IB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.AUTHORS                |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | JB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.AUTHORS                |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | KB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.AUTHORS                |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | MB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.AUTHORS                |    1 |   1.00 |   100.00 |     100.00 | Using where                                     |
 
|    1 | SIMPLE      | RB    | eq_ref | PRIMARY,C__ARRAY_INDEX | PRIMARY | 8       | test.MB.C__COLLECTION_CONTENTS |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | WB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.KEYWORDS               |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | XB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.KEYWORDS               |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | YB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.KEYWORDS               |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | CC    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.KEYWORDS               |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | AC    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.KEYWORDS               |    1 |   1.00 |   100.00 |     100.00 | Using where                                     |
 
|    1 | SIMPLE      | FC    | eq_ref | PRIMARY,C__ARRAY_INDEX | PRIMARY | 8       | test.AC.C__COLLECTION_CONTENTS |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | HB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.FB.C__VALUE               |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | LB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.AUTHORS                |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | JC    | ALL    | NULL                   | NULL    | NULL    | NULL                           |   27 |  27.00 |   100.00 |       9.26 | Using where; Using join buffer (flat, BNL join) |
 
|    1 | SIMPLE      | KC    | eq_ref | PRIMARY                | PRIMARY | 8       | test.JC.C__VALUE               |    1 |   1.00 |   100.00 |      13.33 | Using where                                     |
 
|    1 | SIMPLE      | ZB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.KEYWORDS               |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | LC    | eq_ref | PRIMARY                | PRIMARY | 8       | test.JC.C__VALUE               |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | VB    | ALL    | NULL                   | NULL    | NULL    | NULL                           |   27 |  27.00 |   100.00 |     100.00 | Using join buffer (flat, BNL join)              |
 
+------+-------------+-------+--------+------------------------+---------+---------+--------------------------------+------+--------+----------+------------+-------------------------------------------------+
 
22 rows in set (5.40 sec)

And without one more table, it's nearly instant (0.25 sec):

MariaDB [test]> ANALYZE SELECT * FROM JAVA_LANG_OBJECT AS HB , ORG_CONSERVE_OBJECTS_BOOK AS GB , JAVA_UTIL_SET AS OB , 
JAVA_UTIL_HASHSET AS IB , JAVA_UTIL_ABSTRACTSET AS JB , JAVA_UTIL_ABSTRACTCOLLECTION AS KB , JAVA_LANG_OBJECT AS LB , 
JAVA_UTIL_COLLECTION AS MB , C__ARRAY AS RB , C__ARRAY_MEMBER_JAVA_LANG_OBJECT AS VB , JAVA_UTIL_HASHSET AS WB , 
JAVA_UTIL_ABSTRACTSET AS XB , JAVA_UTIL_ABSTRACTCOLLECTION AS YB , JAVA_LANG_OBJECT AS ZB , JAVA_UTIL_SET AS CC , 
JAVA_UTIL_COLLECTION AS AC , C__ARRAY AS FC , C__ARRAY_MEMBER_JAVA_LANG_OBJECT AS JC , JAVA_LANG_OBJECT AS LC , 
JAVA_LANG_STRING AS KC 
WHERE GB.C__ID = HB.C__ID AND IB.C__ID = JB.C__ID AND JB.C__ID = KB.C__ID AND KB.C__ID = LB.C__ID AND JB.C__ID = OB.C__ID 
AND OB.C__ID = MB.C__ID AND WB.C__ID = XB.C__ID AND XB.C__ID = YB.C__ID AND YB.C__ID = ZB.C__ID AND XB.C__ID = CC.C__ID 
AND CC.C__ID = AC.C__ID AND KC.C__ID = LC.C__ID AND GB.AUTHORS = OB.C__ID AND MB.EMPTY_ = 0 AND MB.C__COLLECTION_CONTENTS = RB.C__ID 
AND GB.KEYWORDS = WB.C__ID AND AC.EMPTY_ = 0 AND AC.C__COLLECTION_CONTENTS = FC.C__ID AND FC.C__ID = JC.C__ARRAY_MEMBER_ID 
AND JC.C__VALUE  = LC.C__ID AND KC.C__VALUE = 'crime';


+------+-------------+-------+--------+------------------------+---------+---------+--------------------------------+------+--------+----------+------------+-------------------------------------------------+
 
| id   | select_type | table | type   | possible_keys          | key     | key_len | ref                            | rows | r_rows | filtered | r_filtered | Extra                                           |
 
+------+-------------+-------+--------+------------------------+---------+---------+--------------------------------+------+--------+----------+------------+-------------------------------------------------+
 
|    1 | SIMPLE      | GB    | ALL    | PRIMARY                | NULL    | NULL    | NULL                           |    6 |   6.00 |   100.00 |     100.00 | Using where                                     |
 
|    1 | SIMPLE      | OB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.AUTHORS                |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | IB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.AUTHORS                |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | JB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.AUTHORS                |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | KB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.AUTHORS                |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | MB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.AUTHORS                |    1 |   1.00 |   100.00 |     100.00 | Using where                                     |
 
|    1 | SIMPLE      | RB    | eq_ref | PRIMARY,C__ARRAY_INDEX | PRIMARY | 8       | test.MB.C__COLLECTION_CONTENTS |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | WB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.KEYWORDS               |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | XB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.KEYWORDS               |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | YB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.KEYWORDS               |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | CC    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.KEYWORDS               |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | AC    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.KEYWORDS               |    1 |   1.00 |   100.00 |     100.00 | Using where                                     |
 
|    1 | SIMPLE      | FC    | eq_ref | PRIMARY,C__ARRAY_INDEX | PRIMARY | 8       | test.AC.C__COLLECTION_CONTENTS |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | HB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.C__ID                  |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | LB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.AUTHORS                |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | JC    | ALL    | NULL                   | NULL    | NULL    | NULL                           |   27 |  27.00 |   100.00 |       9.26 | Using where; Using join buffer (flat, BNL join) |
 
|    1 | SIMPLE      | KC    | eq_ref | PRIMARY                | PRIMARY | 8       | test.JC.C__VALUE               |    1 |   1.00 |   100.00 |      13.33 | Using where                                     |
 
|    1 | SIMPLE      | ZB    | eq_ref | PRIMARY                | PRIMARY | 8       | test.GB.KEYWORDS               |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | LC    | eq_ref | PRIMARY                | PRIMARY | 8       | test.JC.C__VALUE               |    1 |   1.00 |   100.00 |     100.00 |                                                 |
 
|    1 | SIMPLE      | VB    | ALL    | NULL                   | NULL    | NULL    | NULL                           |   27 |  27.00 |   100.00 |     100.00 | Using join buffer (flat, BNL join)              |
 
+------+-------------+-------+--------+------------------------+---------+---------+--------------------------------+------+--------+----------+------------+-------------------------------------------------+
 
20 rows in set (0.25 sec)

Comment by Erik Berglund [ 2016-11-18 ]

@Elena: Yes, I mean 5.7, more precisely 5.7.16-0ubuntu0.16.10.1.
You're right, the query is killable. I meant to say that the query is not terminated when the querying connection is closed - I don't know if this is supposed to happen on MariaDB. I will amend the bug report.

Comment by Erik Berglund [ 2016-11-20 ]

Just to clarify: the simplified queries posted above do not produce the desired result.

Generated at Thu Feb 08 07:48:47 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.