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

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

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

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

I'll try to provide explain and table create syntax, if this will not help to explain the problems I can upload full tables to support site.

The explain syntax in both cases looks almost the same:

HEAP:

+-------+--------+---------------+---------+---------+-------------+------+- --------------------------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+--------+---------------+---------+---------+-------------+------+- --------------------------------------------+ | kws | range | PRIMARY | PRIMARY | 4 | NULL | 10 | where used; Using temporary; Using filesort | | s | eq_ref | PRIMARY | PRIMARY | 4 | kws.site_id | 1 | | +-------+--------+---------------+---------+---------+-------------+------+- --------------------------------------------+ 2 rows in set (1.25 sec)

MYISAM:

+-------+--------+---------------+---------+---------+-------------+------+- --------------------------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+--------+---------------+---------+---------+-------------+------+- --------------------------------------------+ | kws | range | PRIMARY | PRIMARY | 4 | NULL | 767 | where used; Using temporary; Using filesort | | s | eq_ref | PRIMARY | PRIMARY | 4 | kws.site_id | 1 | | +-------+--------+---------------+---------+---------+-------------+------+- --------------------------------------------+

CREATE TABLE sites ( ID int(10) unsigned NOT NULL auto_increment, USER_ID int(10) unsigned DEFAULT '0' NOT NULL, REG_ID smallint(5) unsigned DEFAULT '0' NOT NULL, CITY_ID int(10) unsigned DEFAULT '0' NOT NULL, LANG_ID smallint(5) unsigned DEFAULT '0' NOT NULL, TZ tinyint(4) DEFAULT '0' NOT NULL, UPDATED timestamp(14), CREATED timestamp(14), STATUS tinyint(3) unsigned DEFAULT '0' NOT NULL, LCHECK tinyint(3) unsigned DEFAULT '0' NOT NULL, LUPDATED timestamp(14), LCHECKED timestamp(14), FAILURE tinyint(3) unsigned DEFAULT '0' NOT NULL, RETRIES tinyint(3) unsigned DEFAULT '0' NOT NULL, NOTIFY tinyint(3) unsigned DEFAULT '0' NOT NULL, URL varchar(80) DEFAULT '' NOT NULL, NAME varchar(90) DEFAULT '' NOT NULL, DESCR varchar(255) DEFAULT '' NOT NULL, LOGOTYPE tinyint(3) unsigned DEFAULT '0' NOT NULL, EMAIL varchar(40) DEFAULT '' NOT NULL, HAS_COUNTER tinyint(3) unsigned DEFAULT '0' NOT NULL, IS_IN_RATING tinyint(3) unsigned DEFAULT '1' NOT NULL, PUBLIC_STATS tinyint(3) unsigned DEFAULT '0' NOT NULL, SERVICE_TYPE tinyint(3) unsigned DEFAULT '0' NOT NULL, SERVICE_TIME int(10) unsigned DEFAULT '0' NOT NULL, COUNTER_CREATE timestamp(14), COUNTER_RESET timestamp(14), P_HITS int(10) unsigned DEFAULT '0' NOT NULL, P_HOSTS int(10) unsigned DEFAULT '0' NOT NULL, P_VISITS int(10) unsigned DEFAULT '0' NOT NULL, P_VISITORS int(10) unsigned DEFAULT '0' NOT NULL, P_JUMPS int(10) unsigned DEFAULT '0' NOT NULL, P_COMMINGS int(10) unsigned DEFAULT '0' NOT NULL, FRAMED tinyint(3) unsigned DEFAULT '0' NOT NULL, code_type tinyint(4) DEFAULT '0' NOT NULL, code_size tinyint(4) DEFAULT '0' NOT NULL, code_parts tinyint(4) DEFAULT '0' NOT NULL, LOGS_FROM timestamp(14), IS_LISTED tinyint(3) unsigned DEFAULT '0' NOT NULL, LISTED tinyint(3) unsigned DEFAULT '1' NOT NULL, MODERATE tinyint(4) unsigned DEFAULT '0' NOT NULL, MD_TS timestamp(14), AUTOMODERATE tinyint(3) unsigned DEFAULT '0' NOT NULL, MODERATOR smallint(5) unsigned DEFAULT '0' NOT NULL, SNAME varchar(90) DEFAULT '' NOT NULL, SURL varchar(80) DEFAULT '' NOT NULL, SDESCR varchar(255) DEFAULT '' NOT NULL, SLANG_ID smallint(5) unsigned DEFAULT '0' NOT NULL, SREG_ID smallint(5) unsigned DEFAULT '0' NOT NULL, DELETETIME timestamp(14), blocked tinyint(3) unsigned DEFAULT '0' NOT NULL, registrant smallint(5) unsigned DEFAULT '0' NOT NULL, INDEXED timestamp(14), YANDEX_SITEID int(10) unsigned DEFAULT '0' NOT NULL, subscribers int(10) unsigned DEFAULT '0' NOT NULL, code_diff tinyint(4) unsigned DEFAULT '0' NOT NULL, count_plugins timestamp(14), PRIMARY KEY (ID), KEY USER_ID (USER_ID), KEY LANG_ID (LANG_ID), KEY REG_ID (REG_ID), KEY CITY_ID (CITY_ID), KEY updated (UPDATED), KEY created (CREATED) )/*! type=MyISAM */;

CREATE TABLE k01_kwsites ( site_id int(10) unsigned DEFAULT '0' NOT NULL, keyword_id int(10) unsigned DEFAULT '0' NOT NULL, site_visitors int(10) unsigned DEFAULT '0' NOT NULL, hits int(10) unsigned DEFAULT '0' NOT NULL, duration int(10) unsigned DEFAULT '0' NOT NULL, sessions int(10) unsigned DEFAULT '0' NOT NULL, aver_sess_len int(10) unsigned DEFAULT '0' NOT NULL, last timestamp(14), sort int(10) unsigned DEFAULT '0' NOT NULL, status int(10) unsigned DEFAULT '0' NOT NULL, PRIMARY KEY (keyword_id,site_id) )/*! type=MyISAM delay_key_write=1 */;