Bug #4113 Optimizer cannot figure out how to use a PRIMARY in a HEAP table

• Submitted: 11 Jun 2004 20:47
• Modified: 12 Jun 2004 6:18
• Reporter: Dathan Pattishall
• Status: Not a Bug
• Category: MySQL Server: MyISAM storage engine
• Severity: S1 (Critical)
• Version: 4.0.18
• OS: Linux (Linux dbuser1.gbxsc.friendster.c)
• CPU Architecture: Any

[11 Jun 2004 20:47] Dathan Pattishall

Description:
explain SELECT *  from potentialfriends  where originator=3650907 and respondant=7529501;            
+-----------------------------------------------------+
| Comment                                             |
+-----------------------------------------------------+
| Impossible WHERE noticed after reading const tables |
+-----------------------------------------------------+

CREATE TABLE `potentialfriends` (
  `originator` int(10) NOT NULL default '0',
  `respondant` int(10) NOT NULL default '0',
  PRIMARY KEY  (`originator`,`respondant`),
  KEY `respondant` (`respondant`)
) TYPE=HEAP MAX_ROWS=2431504384 AVG_ROW_LENGTH=50

explain select count(*) from potentialfriends where originator = 101;          
+------------------+------+---------------+------+---------+------+---------+-------------+
| table            | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+------------------+------+---------------+------+---------+------+---------+-------------+
| potentialfriends | ALL  | PRIMARY       | NULL |    NULL | NULL | 4102986 | Using where |
+------------------+------+---------------+------+---------+------+---------+-------------+

As you can see the HEAP (MEMORY) table cannot use the PRIMARY KEY or the left most prefix of a PRIMARY KEY on lookups.,

How to repeat:

Create the table above fill it with data. 
Run the explain

Suggested fix:
Focus on the optimizer and how it interacts with the HEAP storage engine.

[11 Jun 2004 20:52] Dathan Pattishall

BTW this problem does not effect a MYISAM or INNODB version of the same table.

[11 Jun 2004 20:53] Peter Zaitsev

This is not a bug.

In MySQL 4.0.x  HEAP table have only HASH indexes supported which are only helpful 
with full match. No prefix or range match may use indexes.

In your case you can just add extra index on the first keypart.

[11 Jun 2004 20:56] Dathan Pattishall

if I add a key to the table 

ALTER TABLE potentialfriends add index (originator);
SELECT * FROM Query OK, 5472562 rows affected (25.53 sec)
Records: 5472562  Duplicates: 0  Warnings: 0

xplain SELECT * FROM potentialfriends  where originator=101;
+------------------+------+--------------------+------------+---------+-------+------+-------------+
| table            | type | possible_keys      | key        | key_len | ref   | rows | Extra       |
+------------------+------+--------------------+------------+---------+-------+------+-------------+
| potentialfriends | ref  | PRIMARY,originator | originator |       4 | const |   10 | Using where |
+------------------+------+--------------------+------------+---------+-------+------+-------------+

it works, but this sucks if I had to do this.

[12 Jun 2004 6:18] Sergei Golubchik

another possibility is, of course, to upgrade to 4.1 - where HEAP tables support binary tree indexes - and thus can do prefix searches