I've got a query that's running very slow. Looks like MySQL isn't using the indexes that I've set up. I've toyed a bit with making a combined index, which it seems to use for one table, but I can't get it to use an index on the other. Any thoughts?
mysql> explain extended SELECT * FROM territory_borders_finished tb, territories_finished te WHERE tb.tb_game_number = 379085 AND te.te_game_number = tb.tb_game_number AND tb.tb_border = te.te_code;
+----+-------------+----------------------------+------+-----------------------+-------------+---------+--------------------------------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------------------+------+-----------------------+-------------+---------+--------------------------------------------------+------+-------------+
| 1 | SIMPLE | territory_borders_finished | ref | tb_idx_gn | tb_idx_gn | 4 | const | 89 | |
| 1 | SIMPLE | territories_finished | ref | te_idx_code,te_idx_gn | te_idx_code | 4 | lg_db.territory_borders_finished.tb_border | 1 | Using where |
+----+-------------+----------------------------+------+-----------------------+-------------+---------+--------------------------------------------------+------+-------------+
2 rows in set, 1 warning (0.00 sec)
mysql> show create table territory_borders_finished;
+----------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| territory_borders_finished | CREATE TABLE `territory_borders_finished` (
`tb_game_number` int(11) NOT NULL default '0',
`tb_territory` int(11) NOT NULL default '0',
`tb_border` int(11) NOT NULL default '0',
KEY `tb_idx_ter` (`tb_territory`),
KEY `tb_idx_gn` (`tb_game_number`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+----------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table territories_finished;
+----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| territories_finished | CREATE TABLE `territories_finished` (
`te_game_number` int(11) NOT NULL default '0',
`te_code` int(11) NOT NULL default '0',
`te_name` varchar(64) NOT NULL default '',
`te_owner` int(11) NOT NULL default '0',
`te_armies` int(11) NOT NULL default '0',
`te_continent` int(11) NOT NULL default '0',
`te_center_x` int(11) NOT NULL default '0',
`te_center_y` int(11) NOT NULL default '0',
`te_fortress` int(11) NOT NULL default '0',
`te_capitol` int(11) NOT NULL default '0',
`te_leader` int(11) NOT NULL default '0',
KEY `te_idx_code` (`te_code`),
KEY `te_idx_name` (`te_name`),
KEY `te_idx_gn` (`te_game_number`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show table status like 'territories_finished';
+----------------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| 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 |
+----------------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| territories_finished | MyISAM | 10 | Dynamic | 19237497 | 44 | 864182464 | 281474976710655 | 661663744 | 0 | NULL | 2009-03-12 23:28:03 | 2011-02-22 23:25:06 | 2009-03-15 15:41:56 | latin1_swedish_ci | NULL | | |
+----------------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
1 row in set (0.01 sec)
mysql> show table status like 'territory_borders_finished';
+----------------------------+--------+---------+------------+----------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| 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 |
+----------------------------+--------+---------+------------+----------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| territory_borders_finished | MyISAM | 10 | Fixed | 71909549 | 13 | 934824137 | 3659174697238527 | 1978435584 | 0 | NULL | 2009-03-12 23:28:03 | 2011-02-22 23:25:07 | 2009-03-15 15:41:56 | latin1_swedish_ci | NULL | | |
+----------------------------+--------+---------+------------+----------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE '%buffer%';
+-------------------------------+------------+
| Variable_name | Value |
+-------------------------------+------------+
| bdb_log_buffer_size | 524288 |
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 8388608 |
| innodb_log_buffer_size | 1048576 |
| join_buffer_size | 131072 |
| key_buffer_size | 4294967296 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 2097144 |
+-------------------------------+------------+
13 rows in set (0.00 sec)