Not the answer you need?
Register and ask your own question!

MySQL Query Optimization - Trouble with Indices

gangof8gangof8 EntrantInactive User Role Beginner
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=[EMAIL="'mycustomer@yahoo.com'"]'[email protected]'[/EMAIL]
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=[EMAIL="'mycustomer@yahoo.com'"]'[email protected]'[/EMAIL]
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 ) )

Comments

  • gmousegmouse Mod Squad Inactive User Role Beginner
    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.
  • gangof8gangof8 Entrant Inactive User Role Beginner
    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=[EMAIL="'mycustomer@yahoo.com'"]'[email protected]'[/EMAIL]
    and prod_det.status='completed' and prod_det.isRated18=false;
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.