For some strange reason, all HEAP table indexes only process WHERE clauses
with = and <=> operators ONLY. There's nothing you can do about it. I'm
pretty sure of that because I remember the MySQL mentions something like
this about HEAP tables.
-----Original Message-----
From: Jeff S Wheeler [mailto:[EMAIL PROTECTED]]
Sent: March 8, 2001 9:11 PM
To: [EMAIL PROTECTED]
Subject: indexes on TEMPORARY HEAP tables ignored in ORDER BY / GROUP BY
Hi, I'm not subscribed to the list so please CC: me on replies.
Why are indexes on TEMPORARY tables created with TYPE=HEAP ignored when
doing a query involving ORDER BY or GROUP BY? These indexes are used in
some selects involving a WHERE clause, so obviously the index isn't
completely worthless. Why is it not used in cases when the index would be
used if it were an on-disk table?
My disk subsystem is not fast enough to do these queries efficiently, but I
do have enough memory to create the temp table with TYPE=HEAP. But, it
would improve performance signifigantly if indexes were not ignored. I know
this because if I do create a disk table and then query against it while it
is in os-disk-cache, the queries are actually faster than the TYPE=HEAP
table, because mysql will use the index effectively.
Thanks, SQL statements and .sig to follow.
mysql> CREATE TEMPORARY TABLE t2 (CallCount INT NOT NULL, BTN CHAR(24) NOT
NULL) TYPE=HEAP;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t2 SELECT COUNT(*) AS CallCount, BillN AS BTN FROM Call
GROUP BY BillN;
Query OK, 5617334 rows affected (40.50 sec)
mysql> CREATE INDEX CallCount ON t2 (CallCount);
Query OK, 5617334 rows affected (13.76 sec)
mysql> EXPLAIN SELECT * FROM t2 USE INDEX (CallCount) ORDER BY CallCount
DESC LIMIT 10;
+-------+------+---------------+------+---------+------+---------+----------
------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
|
+-------+------+---------------+------+---------+------+---------+----------
------+
| t2 | ALL | NULL | NULL | NULL | NULL | 5617334 | Using
filesort |
+-------+------+---------------+------+---------+------+---------+----------
------+
1 row in set (0.00 sec)
mysql> SHOW INDEX FROM t2;
+-------+------------+-----------+--------------+-------------+-----------+-
------------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
Cardinality | Sub_part | Packed | Comment |
+-------+------------+-----------+--------------+-------------+-----------+-
------------+----------+--------+---------+
| t2 | 1 | CallCount | 1 | CallCount | NULL |
NULL | NULL | NULL | |
+-------+------------+-----------+--------------+-------------+-----------+-
------------+----------+--------+---------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM t2 WHERE CallCount=10;
+-------+------+---------------+-----------+---------+-------+------+-------
-----+
| table | type | possible_keys | key | key_len | ref | rows | Extra
|
+-------+------+---------------+-----------+---------+-------+------+-------
-----+
| t2 | ref | CallCount | CallCount | 4 | const | 10 | where
used |
+-------+------+---------------+-----------+---------+-------+------+-------
-----+
1 row in set (0.00 sec)
---
Jeff S Wheeler [EMAIL PROTECTED]
Software Development Five Elements, Inc
http://www.five-elements.com/~jsw/
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
