Need help on query optimization

MySQL & MariaDB Other MySQL® Questions
1

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]

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