Hi Folks,

I've come across a serious problem with indexing. I've two queries with exactly similar where clauses. However, one of them returns the count and the other returns the records. The problem is, MySQL uses different indexes for both of them.

I wonder if someone could, please, shed some light on this.

Many thanks in advance

-------------------------------------------------
DROP TABLE IF EXISTS `products`.`prod_details`;
CREATE TABLE `products`.`prod_details` (
`prodId` bigint(20) NOT NULL,
`source` varchar(50) default NULL,
`text1` text,
`text2` text,
`leafCatgId` bigint(20) NOT NULL,
`status` enum('incomplete','embargoed','waiting_for_activat ion',
'completed','deleted') NOT NULL default 'incomplete',
`createdAt` datetime NOT NULL default '0000-00-00 00:00:00',
`email` varchar(100) default NULL,
`isRated18` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`prodId`),
KEY `i_leafcls_status` (`leafCatgId`,`status`),
KEY `i_email_status` (`email`,`status`,`isRated18`,`createdAt`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

I'm using MySQL 4.X

-------------------------------------------------
prod_details table contains 1.5 million records and the associated prod_category table contains about 200 records, where categoryId is the PK.
-------------------------------------------------

explain select * from prod_details prod_det
inner join prod_category prod_cat on prod_det.leafCatId=prod_cat.categoryId
where prod_cat.hideDetails=false and prod_det.email='mycustomer@yahoo.com'
and prod_det.status='completed' and prod_det.isRated18=false order by prod_det.createdAt desc limit 20;

id:1
select_type:SIMPLE
tablerod_det
type:ref
possible_keys:i_email_status
key:i_email_status
key_len:103
ref:const,const,const
rows:45912
Extras:Using where

id:1
select_type:SIMPLE
table: prod_cat
type: eq_ref
possible_keys:PRIMARY,hideDetails
key: PRIMARY
key_len:8
refroducts.prod_det.leafCatgId
rows:1
Extras:Using where

This query uses the index i_email_status and is very efficient.

-------------------------------------------------
explain select count(*) from prod_details prod_det
inner join prod_category prod_cat on prod_det.leafCatId=prod_cat.categoryId
where prod_cat.hideDetails=false and prod_det.email='mycustomer@yahoo.com'
and prod_det.status='completed' and prod_det.isRated18=false;

id:1
select_type:SIMPLE
table: prod_cat
type: eq_ref
possible_keys:PRIMARY,hideDetails
key: hideDetails
key_len:2
ref:const
rows:300
Extras:Using where

id:1
select_type:SIMPLE
tablerod_det
type:ref
possible_keys: i_leafcls_status,i_email_status
key: i_leafcls_status
key_len:10
refroducts.prod_cat.categoryId,const
rows:234
Extras:Using where

This query is exactly similar to the previous query; however, returns only the count. I'm wondering why is this query using i_leafcls_status index instead of using i_email_status? This is taking upto 5 mins to process (real shame on me ) )
-------------------------------------------------