Order By Clause does not use composite index

[ziploc1010]

Hi,
I have created a composite index with the following query:

CREATE INDEX `idx_Object-id_all`
ON `Object-id` (id, str);

When I try to select from the `Object-id` table, it does not allow me to use the index and applys a filesort.

explain select id, str from `entrez_gene`.`Object-id` order by id,str;
+----+-------------+-----------+------+---------------+------+---------+------+----------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+----------+----------------+
| 1 | SIMPLE | Object-id | ALL | NULL | NULL | NULL | NULL | 72650543 | Using filesort |
+----+-------------+-----------+------+---------------+------+---------+------+----------+----------------+
1 row in set (0.00 sec)

If I change the query to only select with one column, it will use the composite index:

explain select id, str from `entrez_gene`.`Object-id` order by id;
+----+-------------+-----------+-------+---------------+-------------------+---------+------+----------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+-------------------+---------+------+----------+-------+
| 1 | SIMPLE | Object-id | index | NULL | idx_Object-id_all | 775 | NULL | 72650543 | |
+----+-------------+-----------+-------+---------------+-------------------+---------+------+----------+-------+
1 row in set (0.00 sec)

I have been unsuccessful using index hints, such as "use index" and "force index"

Here is the create table and index statement for `Object-id`:
describe `Object-id`
-> ;
+--------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+-------+
| Object-id_id | int(11) | NO | PRI | 0 | |
| id | int(11) | YES | MUL | NULL | |
| str | varchar(1000) | YES | | NULL | |
+--------------+---------------+------+-----+---------+-------+
3 rows in set (0.02 sec)



show index from `Object-id`;
+-----------+------------+-------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+-------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| Object-id | 0 | PRIMARY | 1 | Object-id_id | A | 73716061 | NULL | NULL | | BTREE | |
| Object-id | 1 | idx_Object-id_all | 1 | id | A | 73716061 | NULL | NULL | YES | BTREE | |
| Object-id | 1 | idx_Object-id_all | 2 | str | A | 73716061 | 767 | NULL | YES | BTREE | |
+-----------+------------+-------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.27 sec)

Here is the mysql version:
show variables like "%version%";
+-------------------------+------------------------------------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------------------------------------+
| protocol_version | 10 |
| version | 5.0.77 |
| version_bdb | Sleepycat Software: Berkeley DB 4.1.24: (January 29, 2009) |
| version_comment | Source distribution |
| version_compile_machine | x86_64 |
| version_compile_os | redhat-linux-gnu |
+-------------------------+------------------------------------------------------------+
6 rows in set (0.00 sec)

 

[ziploc1010]

The limit clause was only for testing,
and I will need to run the query with only the order by clause.

I do not know why the query will not use the index with two fields.
I tried recreating the index with one field and it still did not work.

I also tried recreating the index with only 500 characters, instead of 1000, but that did not work out as well.
I ran analyze on the table and it did not give me different results.

Here is the output from all of the queries:

CREATE INDEX `idx_Object-id_all`
ON `Object-id` (id)
--------------

Query OK, 74133882 rows affected (51 min 51.02 sec)
Records: 74133882 Duplicates: 0 Warnings: 0

explain select id, str from `entrez_gene`.`Object-id` order by id;
+----+-------------+-----------+-------+---------------+-------------------+---------+------+----------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+-------------------+---------+------+----------+-------+
| 1 | SIMPLE | Object-id | index | NULL | idx_Object-id_all | 5 | NULL | 75289973 | |
+----+-------------+-----------+-------+---------------+-------------------+---------+------+----------+-------+
1 row in set (0.00 sec)

explain select id, str from `entrez_gene`.`Object-id` order by id,str;
+----+-------------+-----------+------+---------------+------+---------+------+----------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+----------+----------------+
| 1 | SIMPLE | Object-id | ALL | NULL | NULL | NULL | NULL | 75289973 | Using filesort |
+----+-------------+-----------+------+---------------+------+---------+------+----------+----------------+
1 row in set (0.01 sec)


explain select id, str from `entrez_gene`.`Object-id` force index(`idx_Object-id_all` ) order by id,str;
+----+-------------+-----------+------+---------------+------+---------+------+----------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+----------+----------------+
| 1 | SIMPLE | Object-id | ALL | NULL | NULL | NULL | NULL | 75289973 | Using filesort |
+----+-------------+-----------+------+---------------+------+---------+------+----------+----------------+
1 row in set (0.00 sec)

--------------
drop index `idx_Object-id_all` on `Object-id`
--------------

Query OK, 74133882 rows affected (10 min 41.57 sec)
Records: 74133882 Duplicates: 0 Warnings: 0

--------------
CREATE INDEX `idx_Object-id_all`
ON `Object-id` (id, str(500))
--------------

Query OK, 74133882 rows affected (1 hour 5 min 1.57 sec)
Records: 74133882 Duplicates: 0 Warnings: 0

mysql> explain select id, str from `entrez_gene`.`Object-id` order by id;
+----+-------------+-----------+-------+---------------+-------------------+---------+------+----------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+-------------------+---------+------+----------+-------+
| 1 | SIMPLE | Object-id | index | NULL | idx_Object-id_all | 508 | NULL | 73343130 | |
+----+-------------+-----------+-------+---------------+-------------------+---------+------+----------+-------+
1 row in set (0.00 sec)

mysql> explain select id, str from `entrez_gene`.`Object-id` order by id,str;
+----+-------------+-----------+------+---------------+------+---------+------+----------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+----------+----------------+
| 1 | SIMPLE | Object-id | ALL | NULL | NULL | NULL | NULL | 73343130 | Using filesort |
+----+-------------+-----------+------+---------------+------+---------+------+----------+----------------+
1 row in set (0.00 sec)