Hi!

Peter> The problem is the followings, in a few words: I'm trying to convert one
of Peter> my tables to HEAP to get some speed, but get wrong result (empty set) Peter> instead. Convertng table back brings everything to right.

Peter> MYISAM: Peter> select s.id, s.name, s.url, sum(kws.hits) as hits, sum(kws.sessions) as Peter> sessions, sum(kws.aver_sess_len) as aver_sess_len, s.descr, sum(kws.sort)
as Peter> sort from rating.sites as s, k01_kwsites as kws where s.id=kws.site_id
and Peter> kws.keyword_id in ( 204) group by kws.site_id order by kws.sort desc
limit Peter> 0, 10 ; Peter> .... Peter> 10 rows in set (4.95 sec)

mysql> alter table k01_kwsites type=heap; Peter> Query OK, 943960 rows affected (8.62 sec) Peter> Records: 943960 Duplicates: 0 Warnings: 0

Peter> HEAP: mysql> select s.id, s.name, s.url, sum(kws.hits) as hits, sum(kws.sessions) Peter> as -> sessions, sum(kws.aver_sess_len) as aver_sess_len, s.descr, Peter> sum(kws.sort) as -> sort from rating.sites as s, k01_kwsites as kws where Peter> s.id=kws.site_id and -> kws.keyword_id in ( 204) group by kws.site_id order by kws.sort desc Peter> limit -> 0, 10 ; Peter> Empty set (0.00 sec)

Sorry for the delay, but I was away for a couple of days in Ukraine..

The bug was that in this case MySQL did try to use a key part for the heap table, which doesn't work as the HEAP tables uses hashing to lookup up index and because of this only works with full keys.

I have now fixed this and the resulting explain is as follows:

mysql> source /my/data/test3/sql; +-------+--------+---------------+---------+---------+-------------+--------+---------------------------------------------+ | table | type | possible_keys | key | key_len | ref | rows |
Extra | +-------+--------+---------------+---------+---------+-------------+--------+---------------------------------------------+ | kws | ALL | PRIMARY | NULL | NULL | NULL | 943960 |
where used; Using temporary; Using filesort | | s | eq_ref | PRIMARY | PRIMARY | 4 | kws.site_id | 1 |
| +-------+--------+---------------+---------+---------+-------------+--------+---------------------------------------------+ 2 rows in set (0.16 sec)

Unfortunately this probably makes heap keys impossible for you to use with this query, if you don't add a key on kws.keyword_id.

If you want the patch, mail me; If not, it will be in 3.23.23 shortly...

Regards, Monty