Announcement

Announcement Module
Collapse
No announcement yet.

Need help on query optimization

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

  • Need help on query optimization

    Can you please suggest what is the best way to optimize the queries having count or sum functions

    Tables A,B

    Index Information

    Table A
    date_id : primary key
    year : Index
    quarter : Index
    month : Index

    Table B

    PayNumber and date_id : Primary Key (paynumber,date_id)
    date_id : Index
    status : Index
    center_id : Index

    select
    A.year,
    A.quarter,
    A.month,
    B.status,
    count(distinct B.centre_id) as c_centre_id
    from
    A, B
    where
    B.date_id = A.date_id and
    A.year in ('YR11', 'YR12') and
    B.status = '2'
    group by
    A.year,
    A.quarter,
    A.month,
    B.status;


    Its reading approximatly 10 Million rows to get 5 rows resultset because of count function and its taking 7 to 8 sec.

    Tables are in MyISAM
    MySQL Version : 5.5.x

    Thanks....

  • #2
    Rewrite your query this way instead:

    select A.year, A.quarter, A.month, B.status,COUNT(distinct B.centre_id) AS c_centre_idFROMAINNER JOIN B ON B.date_id = A.date_id AND B.status = '2'WHEREA.year in ('YR11', 'YR12')GROUP BY A.year, A.quarter, A.month, B.status

    It makes is a lot easier to read.

    And please provide the output from:

    SHOW CREATE TABLE [yourTableNameHere]

    and the output from:

    EXPLAIN [your query here]

    Comment


    • #3
      EXPLAIN PLAN

      id select_type table type possible_keys key key_len ref rows Extra
      1 SIMPLE A ALL PRIMARY,year 1037 Using where; Using temporary; Using filesort
      1 SIMPLE B ref date_id,status date_id 4 test.A.date_id 1521 Using where

      one correction : Its reading approximately 1 Million rows to get 5 rows resultset because of count function and its taking 7 to 8 sec.

      Table B
      CREATE TABLE `B` (
      `paynumber` varchar(20) NOT NULL DEFAULT '',
      `date_id` int(11) unsigned NOT NULL,
      `Amount` double DEFAULT NULL,
      `LoanStartDate` date DEFAULT NULL,
      `centre_id` bigint(20) unsigned NOT NULL,
      `category_id` bigint(20) DEFAULT NULL,
      `Towntype` varchar(40) DEFAULT NULL,
      `groupname` char(7) NOT NULL,
      `status` tinyint(3) unsigned NOT NULL,
      `checks` tinyint(11) DEFAULT NULL,
      PRIMARY KEY (`paynumber`,`date_id`),
      KEY `paynumber` (`LoanRefNumber`),
      KEY `date_id` (`date_id`),
      KEY `centre_id` (`centre_id`),
      KEY `category_id` (`category_id`),
      KEY `status` (`status`),
      KEY `groupname` (`groupname`),
      KEY `checks` (`checks`)
      ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC

      Table A

      CREATE TABLE `A` (
      `date_id` int(10) unsigned NOT NULL DEFAULT '0',
      `year` varchar(10) DEFAULT NULL,
      `month` int(10) unsigned DEFAULT NULL,
      `quarter` int(10) unsigned DEFAULT NULL,
      PRIMARY KEY (`date_id`) USING BTREE,
      KEY `year` (`year`),
      KEY `year` (`month`),
      KEY `year` (`quarter`)
      ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC

      Comment

      Working...
      X