MySQL Forums
Forum List  »  Performance

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
2548
February 23, 2011 01:31AM
794
February 23, 2011 08:16AM
785
February 23, 2011 09:58AM
806
February 24, 2011 09:26AM
793
February 24, 2011 12:25PM
1086
February 24, 2011 03:36PM
788
February 24, 2011 06:57PM
1215
February 24, 2011 08:49PM
704
February 25, 2011 12:52AM
722
February 25, 2011 07:44AM
673
February 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.