MySQL Forums
Forum List  »  Optimizer & Parser

possible_keys exist but key is NULL
Posted by: David McLaughlin
Date: February 22, 2006 11:29AM

Kind of new here. Your patience is appreciated.

I'm doing something of the sort:

mysql> CREATE TABLE `test` ( `a` INT KEY, `b` INT, `c` INT );
mysql> CREATE INDEX `my_index` on `test` ( `b`, `c` );
mysql> INSERT INTO `test` (`b`, `c` ) VALUES( 1223, 123 );
mysql> EXPLAIN SELECT * FROM `test` WHERE `b` > 50 AND `c` > 100;
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | test | system | my_index | NULL | NULL | NULL | 1 | |
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

Of course, my real tables have millions of rows, etc.

The problem is there is a "possible_key" but no key is chosen.
I can force the key choice using FORCE INDEX (and the query becomes efficient), but I don't want to "force" all my database programs to worry about what I call my indexes and whether they need to force an index, etc.

ANALYZE TABLE doesn't help.

Any help is appreciated,
Thanks,
David McLaughlin

Options: ReplyQuote


Subject
Views
Written By
Posted
possible_keys exist but key is NULL
10205
February 22, 2006 11:29AM


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.