GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

MySQL Query Optimization - Trouble with Indices

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • MySQL Query Optimization - Trouble with Indices

    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 ) )
    -------------------------------------------------

  • #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.

    Comment


    • #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;

      Comment

      Working...
      X