Hi, I need your opinion about optimizing this query... I would really appreciate your help on this:
The following table saves data about views to certain object identified by (cod-campanha,cod_tema,cod_canal,tipocanal,localizacao,seccao) from users. Each tuple (cod-campanha,cod_tema,cod_canal,tipocanal,localizacao,seccao, user) appears only once, and views is count of times he as seen the object. CREATE TABLE `users_views2` ( `cod_campanha` int(10) unsigned NOT NULL default '0', `cod_tema` int(10) unsigned NOT NULL default '0', `cod_canal` int(10) unsigned NOT NULL default '0', `tipocanal` tinyint(3) unsigned NOT NULL default '0', `localizacao` tinyint(3) unsigned NOT NULL default '0', `seccao` tinyint(3) unsigned NOT NULL default '0', `user` varchar(33) NOT NULL default '', `views` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`cod_campanha`,`cod_tema`,`cod_canal`,`tipocanal`,`localizacao`,`seccao`,`user`), KEY `users_views_idx2` (`cod_campanha`,`user`,`cod_canal`,`localizacao`,`seccao`), KEY `users_views_idx3` (`cod_campanha`,`user`,`tipocanal`) ) TYPE=MyISAM PACK_KEYS=1 mysql> explain select cod_campanha, count(distinct user) as users, cod_tema from users_views2 where cod_campanha in (16,35,72,80,83,86,92,94,107,108,114,115,118,112) group by cod_campanha,cod_tema; +--------------+-------+-------------------------------------------+---------+---------+------+---------+-------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +--------------+-------+-------------------------------------------+---------+---------+------+---------+-------------------------+ | users_views2 | range | PRIMARY,users_views_idx2,users_views_idx3 | PRIMARY | 4 | NULL | 2480655 | where used; Using index | +--------------+-------+-------------------------------------------+---------+---------+------+---------+-------------------------+ 1 row in set (0.01 sec) I don't think the indexes created are very well.. Can you give me an opinion about that ? Thanx, -- dsoares (sql) --------------------------------------------------------------------- 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
