MySQL Query Optimization - Trouble with Indices

MySQL & MariaDB Other MySQL® Questions
1

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_activation’,
‘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
table:prod_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
ref:products.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
table:prod_det
type:ref
possible_keys: i_leafcls_status,i_email_status
key: i_leafcls_status
key_len:10
ref:products.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 ) )

2

I have no clue why MySQL choses this table order (given this table order, the choice of indices makes sense), but try straight_join instead of inner join.

3

Many thanks. I’ve tried using straight_join and the MySQL surely changed the table order. I could understand to some extent; not fully, though. perhaps I should google for more details. However, the new query is taking 5 seconds now:

explain select count(prod_det.leafCatId) from prod_details prod_det
straight_join prod_category prod_cat where prod_det.leafCatId=prod_cat.categoryId
and prod_cat.hideDetails=false and prod_det.email=‘mycustomer@yahoo.com’
and prod_det.status=‘completed’ and prod_det.isRated18=false;