MySQL Forums
Forum List  »  MySQL Query Browser

Re: Slow Query Performance / SELECT DISTINCT w/COUNTs
Posted by: JP Howlett
Date: March 12, 2012 03:41PM

Hi, thank you for your reply. I was just about to post that I found a solution, although I don't know why it works better, but the query below outperforms the one I posted (it takes less than a second to complete):
SELECT clients.county AS cn, COUNT(*) AS cnt
FROM clients LEFT JOIN list_crit_county ON clients.county = list_crit_county.county
WHERE clients.state LIKE 'NJ' AND list_crit_county.county IS NULL
GROUP BY cn
ORDER BY cn

In case it warrants further discussion or you can shed light on why or additional improvements, here is the output as you requested:

As you can see, I have some indexes that should probably be dropped now -- I was just trying everything I could think of.

clients:
CREATE TABLE `clients` (
`id` int(10) unsigned NOT NULL auto_increment,
`name` varchar(50) NOT NULL,
`address` varchar(50) default NULL,
`attn` varchar(50) NOT NULL,
`city` varchar(50) NOT NULL,
`state` char(2) NOT NULL,
`zip` char(5) NOT NULL,
`zip4` char(4) default NULL,
`county` varchar(50) NOT NULL,
`phone` char(15) default NULL,
`ext` char(6) default NULL,
`fax` char(15) default NULL,
`total_students` mediumint(9) default NULL,
`school_level` char(10) default NULL,
`low_grade` char(15) default NULL,
`high_grade` char(15) default NULL,
`type` char(10) default NULL,
`target_market` char(1) default NULL,
`created` timestamp NULL default CURRENT_TIMESTAMP,
`modified` datetime default NULL,
`flag` tinyint(1) NOT NULL,
`client` tinyint(1) NOT NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`),
KEY `zip` (`zip`),
KEY `county` (`county`),
KEY `address1` (`address`),
KEY `phone` (`phone`),
KEY `state` (`state`),
KEY `target_market` (`target_market`),
KEY `total_students` (`total_students`),
KEY `refinecity` (`state`,`county`,`city`),
KEY `test` (`state`,`county`)
) ENGINE=MyISAM AUTO_INCREMENT=188045 DEFAULT CHARSET=latin1

list_crit_county:
CREATE TABLE `list_crit_county` (
`county` varchar(50) NOT NULL,
PRIMARY KEY (`county`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

(there are only 2 tables referenced).

Thank you for your help.

Options: ReplyQuote


Subject
Written By
Posted
Re: Slow Query Performance / SELECT DISTINCT w/COUNTs
March 12, 2012 03:41PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.