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
