MySQL Forums :: Performance :: MySQL not using indexes


Advanced Search

MySQL not using indexes
Posted by: Darrell Esau ()
Date: February 23, 2011 01:31AM

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)



Options: ReplyQuote


Subject Views Written By Posted
MySQL not using indexes 2475 Darrell Esau 02/23/2011 01:31AM
Re: MySQL not using indexes 779 Shawn Taylor 02/23/2011 08:16AM
Re: MySQL not using indexes 769 Darrell Esau 02/23/2011 09:58AM
Re: MySQL not using indexes 789 Rick James 02/24/2011 09:26AM
Re: MySQL not using indexes 773 Darrell Esau 02/24/2011 12:25PM
Re: MySQL not using indexes 1068 Rick James 02/24/2011 03:36PM
Re: MySQL not using indexes 765 Darrell Esau 02/24/2011 06:57PM
Re: MySQL not using indexes 1205 Rick James 02/24/2011 08:49PM
Re: MySQL not using indexes 692 Darrell Esau 02/25/2011 12:52AM
Re: MySQL not using indexes 707 Rick James 02/25/2011 07:44AM
Re: MySQL not using indexes 660 Darrell Esau 02/25/2011 11:40AM


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.