Mysql,
I have table structure like this:
CREATE TABLE `promo_2003_1` (
`k1` char(17) NOT NULL default '',
`fname` char(3) default '',
`lname` char(6) default '',
`sndx_lname` char(6) default '',
`suffix` char(5) default '',
`zip5` char(5) default '',
`zip4` char(4) default '',
`firstname` char(30) default '',
`lastname` char(30) default '',
`subcode` char(2) default '',
`usps` char(1) default '',
`promodate` date default '0000-00-00',
KEY `zip5` (`zip5`,`zip4`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
When I explain those two queries:
1.
explain select zip5, zip4, count(*),
sum(if(usps='R',1,0)),sum(if(usps='P',1,0)), sum(if(usps='S',1,0)),
sum(if(usps='H',1,0)), sum(if(subcode in
('AB','AD','AE','BC','BF','BM','CO','DD','DS','EB','EW','FO','GR','HT','KS','MO','NG','NH','OX','PA','PH','RD','RH','RI','ST','TB','TP','TR'),
1, 0)), sum(if(subcode in ('CD','MU','CH'), 1, 0)), sum(if(subcode in
('BA','BD','CF','FG','HE','HW','HL','JP','LX','ML','MT','TC'), 1, 0)),
sum(if(subcode in ('BL','KC', 'TK'), 1, 0)), sum(if(subcode in
('CL','EA','EM','SB','TA'), 1, 0)), sum(if(subcode in ('FI', 'PV'), 1,
0)), sum(if(subcode in ('BA','BD','HE','HW','LX','MT','TC'), 1, 0)),
sum(if(subcode in
('AB','AD','AE','BC','BM','CO','CD','CH','DD','DS','EB','EW','FO','KS','ME','MO','MU','NG','NH','OX','PA','PH','RD','RH',
'RI', 'ST','TB','TP'), 1, 0)), sum(if(subcode in ('BL','KC'), 1, 0)),
sum(if(subcode in ('BF','HT','GR','TR'), 1, 0)), sum(if(subcode in
('CF','HL','ML','TK','WC'), 1, 0)), sum(if(subcode in
('CL','EA','EM','SB','TA'), 1, 0)), sum(if(subcode in ('FG','JP'), 1,
0)), sum(if(subcode in ('BD', 'LX', 'OX', 'BL', 'KC', 'GR', 'TR', 'TK',
'WC', 'FG'), 1, 0)), sum(if(subcode in ('CD', 'DD', 'PH'), 1, 0)),
sum(if(subcode in ('OX', 'PH'), 1, 0)), sum(if(subcode in ('CD', 'DD',
'GR', 'TR', 'TK', 'EA', 'SB', 'FG'), 1, 0)), sum(if(subcode in ('BD',
'LX', 'BL', 'KC'), 1, 0)), sum(if(subcode not in ('OX', 'PH','CD', 'DD',
'GR', 'TR', 'TK', 'EA', 'SB', 'FG','BD', 'LX', 'BL', 'KC'), 1, 0)),
min(promodate), max(promodate),
sum(if(promodate>DATE_SUB(CURDATE(),INTERVAL 91 DAY), 1, 0)),
sum(if(promodate>DATE_SUB(CURDATE(),INTERVAL 182 DAY), 1, 0)),
sum(if(promodate>DATE_SUB(CURDATE(),INTERVAL 365 DAY), 1, 0)),
sum(if(promodate>DATE_SUB(CURDATE(),INTERVAL 730 DAY), 1, 0)) from
promo_2003_2 use index (zip5) where zip5 !='' and zip4 !='' group by
zip5, zip4;
+----+-------------+--------------+------+---------------+------+---------+------+----------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
+----+-------------+--------------+------+---------------+------+---------+------+----------+----------------------------------------------+
| 1 | SIMPLE | promo_2003_1 | ALL | zip5 | NULL |
NULL | NULL | 30000000 | Using where; Using temporary; Using filesort |
+----+-------------+--------------+------+---------------+------+---------+------+----------+-------------------------------
2.
explain select zip5, zip4, count(*) from promo_2003_1 group by zip5, zip4;
+----+-------------+--------------+-------+---------------+------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
+----+-------------+--------------+-------+---------------+------+---------+------+----------+-------------+
| 1 | SIMPLE | promo_2003_1 | index | NULL | zip5 |
11 | NULL | 30000000 | Using index |
+----+-------------+--------------+-------+---------------+------+---------+------+----------+-------------+
Why does query 2 pick the zip5 index, but query 1 doesn't ?
How to speed up the query 1?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
