Innodb: join of large table to small table is slow if small table has less than 7 rows
Posted by: Wenfeng Hu
Date: June 04, 2024 01:43AM
Date: June 04, 2024 01:43AM
I happened to read this post https://forums.mysql.com/read.php?22,620920,620920#msg-620920 of 2014 and I tried and confirmed that it could even be reproduced now in 2024.
I wonder if there's any method to solve this problem or any way to let the optimizer do better?
My MySQL is a RDS instance from one public cloud provider, and the version is :
mysql> show variables like "%innodb_version";
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| innodb_version | 8.0.28 |
+----------------+--------+
1 row in set (0.00 sec)
mysql> show variables like "version";
+---------------+---------------+
| Variable_name | Value |
+---------------+---------------+
| version | 8.0.28-230701 |
+---------------+---------------+
1 row in set (0.00 sec)
Below is how I reproduced the problem(some sql copied from the original post):
#create the large table
CREATE TABLE t1 (id int(11) NOT NULL AUTO_INCREMENT, name varchar(255) NOT NULL, t2_id int(11) NOT NULL, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#create the small table
CREATE TABLE t2 (id int(11) NOT NULL AUTO_INCREMENT, name varchar(255) NOT NULL, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#insert 1 record in the small table
INSERT INTO t2 (name) VALUES ('small table');
#insert 262144 records in the large table; we do this by inserting 1 record and exponentially duplicating the records 18 times
INSERT INTO t1 (name, t2_id) VALUES ('large table', 1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
#we now have 262144 identical rows in t1
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 262144 |
+----------+
1 row in set (0.04 sec)
mysql> select count(*) from t2;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.01 sec)
#run the following query; this runs relatively slow; 0.12s
SELECT t1.id, t1.name, t2.id, t2.name FROM t1, t2 WHERE t1.t2_id=t2.id ORDER BY t1.id LIMIT 0,10;
+----+-------------+----+-------------+
| id | name | id | name |
+----+-------------+----+-------------+
| 1 | large table | 1 | small table |
| 2 | large table | 1 | small table |
| 3 | large table | 1 | small table |
| 4 | large table | 1 | small table |
| 6 | large table | 1 | small table |
| 7 | large table | 1 | small table |
| 8 | large table | 1 | small table |
| 9 | large table | 1 | small table |
| 13 | large table | 1 | small table |
| 14 | large table | 1 | small table |
+----+-------------+----+-------------+
10 rows in set (0.12 sec)
#run explain; the primary keys are NOT used
EXPLAIN format=tree SELECT t1.id, t1.name, t2.id, t2.name FROM t1, t2 WHERE t1.t2_id=t2.id ORDER BY t1.id LIMIT 0,10;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Limit: 10 row(s)
-> Sort: t1.id, limit input to 10 row(s) per chunk
-> Stream results (cost=26302.50 rows=26201)
-> Inner hash join (t1.t2_id = t2.id) (cost=26302.50 rows=26201)
-> Table scan on t1 (cost=2721.35 rows=262010)
-> Hash
-> Table scan on t2 (cost=0.25 rows=1)
|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
#Now, let's add 6 more records to the small table
INSERT INTO t2 (name) VALUES ('small table');
INSERT INTO t2 (name) VALUES ('small table');
INSERT INTO t2 (name) VALUES ('small table');
INSERT INTO t2 (name) VALUES ('small table');
INSERT INTO t2 (name) VALUES ('small table');
INSERT INTO t2 (name) VALUES ('small table');
#We now have 7 rows in the small table
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 262144 |
+----------+
1 row in set (0.02 sec)
mysql> select count(*) from t2;
+----------+
| count(*) |
+----------+
| 7 |
+----------+
1 row in set (0.01 sec)
#run the query again; now it is fast; 0.00s
SELECT t1.id, t1.name, t2.id, t2.name FROM t1, t2 WHERE t1.t2_id=t2.id ORDER BY t1.id LIMIT 0,10;
+----+-------------+----+-------------+
| id | name | id | name |
+----+-------------+----+-------------+
| 1 | large table | 1 | small table |
| 2 | large table | 1 | small table |
| 3 | large table | 1 | small table |
| 4 | large table | 1 | small table |
| 6 | large table | 1 | small table |
| 7 | large table | 1 | small table |
| 8 | large table | 1 | small table |
| 9 | large table | 1 | small table |
| 13 | large table | 1 | small table |
| 14 | large table | 1 | small table |
+----+-------------+----+-------------+
10 rows in set (0.00 sec)
#run explain; the primary keys are used
EXPLAIN format=tree SELECT t1.id, t1.name, t2.id, t2.name FROM t1, t2 WHERE t1.t2_id=t2.id ORDER BY t1.id LIMIT 0,10;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Limit: 10 row(s) (cost=39302.51 rows=10)
-> Nested loop inner join (cost=39302.51 rows=10)
-> Index scan on t1 using PRIMARY (cost=0.00 rows=10)
-> Single-row index lookup on t2 using PRIMARY (id=t1.t2_id) (cost=0.15 rows=1)
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
#More info
mysql> show table status;
+------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| t1 | InnoDB | 10 | Dynamic | 262010 | 42 | 11026432 | 0 | 0 | 4194304 | 458731 | 2024-06-04 15:31:25 | 2024-06-04 15:31:47 | NULL | utf8_general_ci | NULL | | |
| t2 | InnoDB | 10 | Dynamic | 7 | 2340 | 16384 | 0 | 0 | 0 | 8 | 2024-06-04 15:31:30 | 2024-06-04 15:34:20 | NULL | utf8_general_ci | NULL | | |
+------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
2 rows in set (0.01 sec)
mysql> show variables like 'innodb%';
+-----------------------------------------------------+-------------------------+
| Variable_name | Value |
+-----------------------------------------------------+-------------------------+
| innodb_adaptive_flushing | ON |
| innodb_adaptive_flushing_lwm | 10 |
| innodb_adaptive_hash_index | OFF |
| innodb_adaptive_hash_index_parts | 8 |
| innodb_adaptive_max_sleep_delay | 150000 |
| innodb_api_bk_commit_interval | 5 |
| innodb_api_disable_rowlock | OFF |
| innodb_api_enable_binlog | OFF |
| innodb_api_enable_mdl | OFF |
| innodb_api_trx_level | 0 |
| innodb_autoextend_increment | 64 |
| innodb_autoinc_lock_mode | 2 |
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_in_core_file | ON |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 1073741824 |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | all |
| innodb_checksum_algorithm | crc32 |
| innodb_cmp_per_index_enabled | OFF |
| innodb_commit_concurrency | 0 |
| innodb_compression_failure_threshold_pct | 5 |
| innodb_compression_level | 6 |
| innodb_compression_pad_pct_max | 50 |
| innodb_concurrency_tickets | 5000 |
| innodb_data_file_path | ibdata1:128M:autoextend |
| innodb_data_home_dir | |
| innodb_ddl_buffer_size | 1048576 |
| innodb_ddl_threads | 4 |
| innodb_deadlock_detect | ON |
| innodb_dedicated_server | OFF |
| innodb_default_row_format | dynamic |
| innodb_directories | |
| innodb_disable_sort_file_cache | OFF |
| innodb_doublewrite | ON |
| innodb_doublewrite_batch_size | 0 |
| innodb_doublewrite_dir | |
| innodb_doublewrite_files | 2 |
| innodb_doublewrite_pages | 120 |
| innodb_extend_and_initialize | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_per_table | ON |
| innodb_fill_factor | 100 |
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | O_DIRECT |
| innodb_flush_neighbors | 0 |
| innodb_flush_sync | ON |
| innodb_flushing_avg_loops | 30 |
| innodb_force_load_corrupted | OFF |
| innodb_force_recovery | 0 |
| innodb_fsync_threshold | 0 |
| innodb_ft_aux_table | |
| innodb_ft_cache_size | 8000000 |
| innodb_ft_enable_diag_print | OFF |
| innodb_ft_enable_stopword | ON |
| innodb_ft_max_token_size | 84 |
| innodb_ft_min_token_size | 3 |
| innodb_ft_num_word_optimize | 2000 |
| innodb_ft_result_cache_limit | 2000000000 |
| innodb_ft_server_stopword_table | |
| innodb_ft_sort_pll_degree | 2 |
| innodb_ft_total_cache_size | 640000000 |
| innodb_ft_user_stopword_table | |
| innodb_idle_flush_pct | 100 |
| innodb_io_capacity | 12000 |
| innodb_io_capacity_max | 24000 |
| innodb_lock_wait_timeout | 50 |
| innodb_log_buffer_size | 33554432 |
| innodb_log_checksums | ON |
| innodb_log_compressed_pages | ON |
| innodb_log_file_size | 1048576000 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_log_spin_cpu_abs_lwm | 80 |
| innodb_log_spin_cpu_pct_hwm | 50 |
| innodb_log_wait_for_flush_spin_hwm | 400 |
| innodb_log_write_ahead_size | 8192 |
| innodb_log_writer_threads | ON |
| innodb_lru_scan_depth | 2048 |
| innodb_max_dirty_pages_pct | 75.000000 |
| innodb_max_dirty_pages_pct_lwm | 0.000000 |
| innodb_max_purge_lag | 0 |
| innodb_max_purge_lag_delay | 0 |
| innodb_max_undo_log_size | 1073741824 |
| innodb_monitor_disable | |
| innodb_monitor_enable | |
| innodb_monitor_reset | |
| innodb_monitor_reset_all | |
| innodb_numa_interleave | OFF |
| innodb_old_blocks_pct | 37 |
| innodb_old_blocks_time | 1000 |
| innodb_online_alter_log_max_size | 134217728 |
| innodb_open_files | 10240 |
| innodb_optimize_fulltext_only | OFF |
| innodb_page_cleaners | 1 |
| innodb_page_size | 16384 |
| innodb_parallel_read_threads | 4 |
| innodb_print_all_deadlocks | OFF |
| innodb_print_ddl_logs | OFF |
| innodb_purge_batch_size | 300 |
| innodb_purge_rseg_truncate_frequency | 128 |
| innodb_purge_threads | 4 |
| innodb_random_read_ahead | OFF |
| innodb_rds_data_file_purge | OFF |
| innodb_rds_data_file_purge_all_at_shutdown | OFF |
| innodb_rds_data_file_purge_dir | |
| innodb_rds_data_file_purge_immediate | OFF |
| innodb_rds_data_file_purge_interval | 100 |
| innodb_rds_data_file_purge_max_size | 32 |
| innodb_rds_data_lock_info_enabled | OFF |
| innodb_rds_fatal_semaphore_timeout_seconds | 600 |
| innodb_rds_print_data_file_purge_process | OFF |
| innodb_rds_warning_long_semaphore_threshold_seconds | 240 |
| innodb_read_ahead_threshold | 56 |
| innodb_read_io_threads | 4 |
| innodb_read_only | OFF |
| innodb_redo_log_archive_dirs | |
| innodb_redo_log_encrypt | OFF |
| innodb_replication_delay | 0 |
| innodb_rollback_on_timeout | OFF |
| innodb_rollback_segments | 128 |
| innodb_segment_reserve_factor | 12.500000 |
| innodb_sort_buffer_size | 1048576 |
| innodb_spin_wait_delay | 6 |
| innodb_spin_wait_pause_multiplier | 50 |
| innodb_stats_auto_recalc | ON |
| innodb_stats_include_delete_marked | ON |
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | OFF |
| innodb_stats_persistent | ON |
| innodb_stats_persistent_sample_pages | 20 |
| innodb_stats_transient_sample_pages | 8 |
| innodb_status_output | OFF |
| innodb_status_output_locks | OFF |
| innodb_strict_mode | OFF |
| innodb_sync_array_size | 1 |
| innodb_sync_spin_loops | 30 |
| innodb_table_locks | ON |
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
| innodb_temp_tablespaces_dir | ./#innodb_temp/ |
| innodb_thread_concurrency | 0 |
| innodb_thread_sleep_delay | 10000 |
| innodb_tmpdir | |
| innodb_undo_directory | ./ |
| innodb_undo_log_encrypt | OFF |
| innodb_undo_log_truncate | ON |
| innodb_undo_tablespaces | 2 |
| innodb_use_fdatasync | OFF |
| innodb_use_native_aio | ON |
| innodb_validate_tablespace_paths | ON |
| innodb_version | 8.0.28 |
| innodb_write_io_threads | 4 |
+-----------------------------------------------------+-------------------------+
160 rows in set (0.01 sec)
mysql> show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t1 | 0 | PRIMARY | 1 | id | A | 262010 | NULL | NULL | | BTREE | | | YES | NULL |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.04 sec)
mysql> show index from t2;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t2 | 0 | PRIMARY | 1 | id | A | 7 | NULL | NULL | | BTREE | | | YES | NULL |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
I wonder if there's any method to solve this problem or any way to let the optimizer do better?
My MySQL is a RDS instance from one public cloud provider, and the version is :
mysql> show variables like "%innodb_version";
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| innodb_version | 8.0.28 |
+----------------+--------+
1 row in set (0.00 sec)
mysql> show variables like "version";
+---------------+---------------+
| Variable_name | Value |
+---------------+---------------+
| version | 8.0.28-230701 |
+---------------+---------------+
1 row in set (0.00 sec)
Below is how I reproduced the problem(some sql copied from the original post):
#create the large table
CREATE TABLE t1 (id int(11) NOT NULL AUTO_INCREMENT, name varchar(255) NOT NULL, t2_id int(11) NOT NULL, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#create the small table
CREATE TABLE t2 (id int(11) NOT NULL AUTO_INCREMENT, name varchar(255) NOT NULL, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#insert 1 record in the small table
INSERT INTO t2 (name) VALUES ('small table');
#insert 262144 records in the large table; we do this by inserting 1 record and exponentially duplicating the records 18 times
INSERT INTO t1 (name, t2_id) VALUES ('large table', 1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
#we now have 262144 identical rows in t1
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 262144 |
+----------+
1 row in set (0.04 sec)
mysql> select count(*) from t2;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.01 sec)
#run the following query; this runs relatively slow; 0.12s
SELECT t1.id, t1.name, t2.id, t2.name FROM t1, t2 WHERE t1.t2_id=t2.id ORDER BY t1.id LIMIT 0,10;
+----+-------------+----+-------------+
| id | name | id | name |
+----+-------------+----+-------------+
| 1 | large table | 1 | small table |
| 2 | large table | 1 | small table |
| 3 | large table | 1 | small table |
| 4 | large table | 1 | small table |
| 6 | large table | 1 | small table |
| 7 | large table | 1 | small table |
| 8 | large table | 1 | small table |
| 9 | large table | 1 | small table |
| 13 | large table | 1 | small table |
| 14 | large table | 1 | small table |
+----+-------------+----+-------------+
10 rows in set (0.12 sec)
#run explain; the primary keys are NOT used
EXPLAIN format=tree SELECT t1.id, t1.name, t2.id, t2.name FROM t1, t2 WHERE t1.t2_id=t2.id ORDER BY t1.id LIMIT 0,10;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Limit: 10 row(s)
-> Sort: t1.id, limit input to 10 row(s) per chunk
-> Stream results (cost=26302.50 rows=26201)
-> Inner hash join (t1.t2_id = t2.id) (cost=26302.50 rows=26201)
-> Table scan on t1 (cost=2721.35 rows=262010)
-> Hash
-> Table scan on t2 (cost=0.25 rows=1)
|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
#Now, let's add 6 more records to the small table
INSERT INTO t2 (name) VALUES ('small table');
INSERT INTO t2 (name) VALUES ('small table');
INSERT INTO t2 (name) VALUES ('small table');
INSERT INTO t2 (name) VALUES ('small table');
INSERT INTO t2 (name) VALUES ('small table');
INSERT INTO t2 (name) VALUES ('small table');
#We now have 7 rows in the small table
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 262144 |
+----------+
1 row in set (0.02 sec)
mysql> select count(*) from t2;
+----------+
| count(*) |
+----------+
| 7 |
+----------+
1 row in set (0.01 sec)
#run the query again; now it is fast; 0.00s
SELECT t1.id, t1.name, t2.id, t2.name FROM t1, t2 WHERE t1.t2_id=t2.id ORDER BY t1.id LIMIT 0,10;
+----+-------------+----+-------------+
| id | name | id | name |
+----+-------------+----+-------------+
| 1 | large table | 1 | small table |
| 2 | large table | 1 | small table |
| 3 | large table | 1 | small table |
| 4 | large table | 1 | small table |
| 6 | large table | 1 | small table |
| 7 | large table | 1 | small table |
| 8 | large table | 1 | small table |
| 9 | large table | 1 | small table |
| 13 | large table | 1 | small table |
| 14 | large table | 1 | small table |
+----+-------------+----+-------------+
10 rows in set (0.00 sec)
#run explain; the primary keys are used
EXPLAIN format=tree SELECT t1.id, t1.name, t2.id, t2.name FROM t1, t2 WHERE t1.t2_id=t2.id ORDER BY t1.id LIMIT 0,10;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Limit: 10 row(s) (cost=39302.51 rows=10)
-> Nested loop inner join (cost=39302.51 rows=10)
-> Index scan on t1 using PRIMARY (cost=0.00 rows=10)
-> Single-row index lookup on t2 using PRIMARY (id=t1.t2_id) (cost=0.15 rows=1)
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
#More info
mysql> show table status;
+------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| t1 | InnoDB | 10 | Dynamic | 262010 | 42 | 11026432 | 0 | 0 | 4194304 | 458731 | 2024-06-04 15:31:25 | 2024-06-04 15:31:47 | NULL | utf8_general_ci | NULL | | |
| t2 | InnoDB | 10 | Dynamic | 7 | 2340 | 16384 | 0 | 0 | 0 | 8 | 2024-06-04 15:31:30 | 2024-06-04 15:34:20 | NULL | utf8_general_ci | NULL | | |
+------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
2 rows in set (0.01 sec)
mysql> show variables like 'innodb%';
+-----------------------------------------------------+-------------------------+
| Variable_name | Value |
+-----------------------------------------------------+-------------------------+
| innodb_adaptive_flushing | ON |
| innodb_adaptive_flushing_lwm | 10 |
| innodb_adaptive_hash_index | OFF |
| innodb_adaptive_hash_index_parts | 8 |
| innodb_adaptive_max_sleep_delay | 150000 |
| innodb_api_bk_commit_interval | 5 |
| innodb_api_disable_rowlock | OFF |
| innodb_api_enable_binlog | OFF |
| innodb_api_enable_mdl | OFF |
| innodb_api_trx_level | 0 |
| innodb_autoextend_increment | 64 |
| innodb_autoinc_lock_mode | 2 |
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_in_core_file | ON |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 1073741824 |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | all |
| innodb_checksum_algorithm | crc32 |
| innodb_cmp_per_index_enabled | OFF |
| innodb_commit_concurrency | 0 |
| innodb_compression_failure_threshold_pct | 5 |
| innodb_compression_level | 6 |
| innodb_compression_pad_pct_max | 50 |
| innodb_concurrency_tickets | 5000 |
| innodb_data_file_path | ibdata1:128M:autoextend |
| innodb_data_home_dir | |
| innodb_ddl_buffer_size | 1048576 |
| innodb_ddl_threads | 4 |
| innodb_deadlock_detect | ON |
| innodb_dedicated_server | OFF |
| innodb_default_row_format | dynamic |
| innodb_directories | |
| innodb_disable_sort_file_cache | OFF |
| innodb_doublewrite | ON |
| innodb_doublewrite_batch_size | 0 |
| innodb_doublewrite_dir | |
| innodb_doublewrite_files | 2 |
| innodb_doublewrite_pages | 120 |
| innodb_extend_and_initialize | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_per_table | ON |
| innodb_fill_factor | 100 |
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | O_DIRECT |
| innodb_flush_neighbors | 0 |
| innodb_flush_sync | ON |
| innodb_flushing_avg_loops | 30 |
| innodb_force_load_corrupted | OFF |
| innodb_force_recovery | 0 |
| innodb_fsync_threshold | 0 |
| innodb_ft_aux_table | |
| innodb_ft_cache_size | 8000000 |
| innodb_ft_enable_diag_print | OFF |
| innodb_ft_enable_stopword | ON |
| innodb_ft_max_token_size | 84 |
| innodb_ft_min_token_size | 3 |
| innodb_ft_num_word_optimize | 2000 |
| innodb_ft_result_cache_limit | 2000000000 |
| innodb_ft_server_stopword_table | |
| innodb_ft_sort_pll_degree | 2 |
| innodb_ft_total_cache_size | 640000000 |
| innodb_ft_user_stopword_table | |
| innodb_idle_flush_pct | 100 |
| innodb_io_capacity | 12000 |
| innodb_io_capacity_max | 24000 |
| innodb_lock_wait_timeout | 50 |
| innodb_log_buffer_size | 33554432 |
| innodb_log_checksums | ON |
| innodb_log_compressed_pages | ON |
| innodb_log_file_size | 1048576000 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_log_spin_cpu_abs_lwm | 80 |
| innodb_log_spin_cpu_pct_hwm | 50 |
| innodb_log_wait_for_flush_spin_hwm | 400 |
| innodb_log_write_ahead_size | 8192 |
| innodb_log_writer_threads | ON |
| innodb_lru_scan_depth | 2048 |
| innodb_max_dirty_pages_pct | 75.000000 |
| innodb_max_dirty_pages_pct_lwm | 0.000000 |
| innodb_max_purge_lag | 0 |
| innodb_max_purge_lag_delay | 0 |
| innodb_max_undo_log_size | 1073741824 |
| innodb_monitor_disable | |
| innodb_monitor_enable | |
| innodb_monitor_reset | |
| innodb_monitor_reset_all | |
| innodb_numa_interleave | OFF |
| innodb_old_blocks_pct | 37 |
| innodb_old_blocks_time | 1000 |
| innodb_online_alter_log_max_size | 134217728 |
| innodb_open_files | 10240 |
| innodb_optimize_fulltext_only | OFF |
| innodb_page_cleaners | 1 |
| innodb_page_size | 16384 |
| innodb_parallel_read_threads | 4 |
| innodb_print_all_deadlocks | OFF |
| innodb_print_ddl_logs | OFF |
| innodb_purge_batch_size | 300 |
| innodb_purge_rseg_truncate_frequency | 128 |
| innodb_purge_threads | 4 |
| innodb_random_read_ahead | OFF |
| innodb_rds_data_file_purge | OFF |
| innodb_rds_data_file_purge_all_at_shutdown | OFF |
| innodb_rds_data_file_purge_dir | |
| innodb_rds_data_file_purge_immediate | OFF |
| innodb_rds_data_file_purge_interval | 100 |
| innodb_rds_data_file_purge_max_size | 32 |
| innodb_rds_data_lock_info_enabled | OFF |
| innodb_rds_fatal_semaphore_timeout_seconds | 600 |
| innodb_rds_print_data_file_purge_process | OFF |
| innodb_rds_warning_long_semaphore_threshold_seconds | 240 |
| innodb_read_ahead_threshold | 56 |
| innodb_read_io_threads | 4 |
| innodb_read_only | OFF |
| innodb_redo_log_archive_dirs | |
| innodb_redo_log_encrypt | OFF |
| innodb_replication_delay | 0 |
| innodb_rollback_on_timeout | OFF |
| innodb_rollback_segments | 128 |
| innodb_segment_reserve_factor | 12.500000 |
| innodb_sort_buffer_size | 1048576 |
| innodb_spin_wait_delay | 6 |
| innodb_spin_wait_pause_multiplier | 50 |
| innodb_stats_auto_recalc | ON |
| innodb_stats_include_delete_marked | ON |
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | OFF |
| innodb_stats_persistent | ON |
| innodb_stats_persistent_sample_pages | 20 |
| innodb_stats_transient_sample_pages | 8 |
| innodb_status_output | OFF |
| innodb_status_output_locks | OFF |
| innodb_strict_mode | OFF |
| innodb_sync_array_size | 1 |
| innodb_sync_spin_loops | 30 |
| innodb_table_locks | ON |
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
| innodb_temp_tablespaces_dir | ./#innodb_temp/ |
| innodb_thread_concurrency | 0 |
| innodb_thread_sleep_delay | 10000 |
| innodb_tmpdir | |
| innodb_undo_directory | ./ |
| innodb_undo_log_encrypt | OFF |
| innodb_undo_log_truncate | ON |
| innodb_undo_tablespaces | 2 |
| innodb_use_fdatasync | OFF |
| innodb_use_native_aio | ON |
| innodb_validate_tablespace_paths | ON |
| innodb_version | 8.0.28 |
| innodb_write_io_threads | 4 |
+-----------------------------------------------------+-------------------------+
160 rows in set (0.01 sec)
mysql> show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t1 | 0 | PRIMARY | 1 | id | A | 262010 | NULL | NULL | | BTREE | | | YES | NULL |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.04 sec)
mysql> show index from t2;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t2 | 0 | PRIMARY | 1 | id | A | 7 | NULL | NULL | | BTREE | | | YES | NULL |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
Subject
Views
Written By
Posted
Innodb: join of large table to small table is slow if small table has less than 7 rows
164
June 04, 2024 01:43AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.