MySQL MIN GROUP BY on large tables ( > 8000 rows)

Question

I have the following query:

SELECT contact_purl, contact_firstName, contact_lastName, MIN( contact_id ) AS MinID
FROM contacts
WHERE contact_client_id = 1
GROUP BY contact_purl
HAVING COUNT( contact_id ) > 1

The purpose is to find any contacts with a duplicate "contact_purl," and return the first entry.

I'm running into a very strange problem... If the table has less than 8,000 rows, the query will render in less than 1 second. HOWEVER, if the table has more than 8,000 rows, the query will take consistently 338 seconds on average.

Here is the query plan for the table with ~5000 rows:

And for ~8000 rows:

The table...

  CREATE TABLE IF NOT EXISTS `contacts` (
  `contact_id` int(11) NOT NULL AUTO_INCREMENT,
  `contact_client_id` int(11) DEFAULT NULL,
  `contact_sales_id` int(11) DEFAULT NULL,
  `contact_campaign_id` int(11) DEFAULT NULL,
  `contact_purl` varchar(100) NOT NULL,
  `contact_purl1` varchar(50) DEFAULT NULL,
  `contact_purl2` varchar(50) DEFAULT NULL,
  `contact_firstName` varchar(50) NOT NULL,
  `contact_lastName` varchar(50) NOT NULL,
  `contact_organization` varchar(100) DEFAULT NULL,
  `contact_url_organization` varchar(200) DEFAULT NULL,
  `contact_position` varchar(50) DEFAULT NULL,
  `contact_email` varchar(100) DEFAULT NULL,
  `contact_phone` varchar(20) DEFAULT NULL,
  `contact_fax` varchar(20) NOT NULL,
  `contact_address1` varchar(100) DEFAULT NULL,
  `contact_address2` varchar(100) DEFAULT NULL,
  `contact_city` varchar(100) DEFAULT NULL,
  `contact_state` varchar(20) DEFAULT NULL,
  `contact_zip` varchar(10) DEFAULT NULL,
  `contact_IP` varchar(50) DEFAULT NULL,
  `contact_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `contact_pw` varchar(200) NOT NULL,
  `contact_subscribed` varchar(1) NOT NULL DEFAULT 'Y',
  `contact_import` varchar(200) DEFAULT NULL,
  `contacts_c_1` varchar(500) DEFAULT NULL,
  `contacts_c_2` varchar(500) DEFAULT NULL,
  `contacts_c_3` varchar(500) DEFAULT NULL,
  `contacts_c_4` varchar(500) DEFAULT NULL,
  `contacts_c_5` varchar(500) DEFAULT NULL,
  `contacts_c_6` varchar(500) DEFAULT NULL,
  `contacts_c_7` varchar(500) DEFAULT NULL,
  `contacts_c_8` varchar(500) DEFAULT NULL,
  `contacts_c_9` varchar(500) DEFAULT NULL,
  `contacts_c_10` varchar(500) DEFAULT NULL,
  `contacts_c_11` varchar(500) DEFAULT NULL,
  `contacts_c_12` varchar(500) DEFAULT NULL,
  `contacts_c_13` varchar(500) DEFAULT NULL,
  `contacts_c_14` varchar(500) DEFAULT NULL,
  `contacts_c_15` varchar(500) DEFAULT NULL,
  `contacts_c_16` varchar(500) DEFAULT NULL,
  `contacts_c_17` varchar(500) DEFAULT NULL,
  `contacts_c_18` varchar(500) DEFAULT NULL,
  `contacts_c_19` varchar(500) DEFAULT NULL,
  `contacts_c_20` varchar(500) DEFAULT NULL,
  `contacts_c_21` varchar(500) DEFAULT NULL,
  `contacts_c_22` varchar(500) DEFAULT NULL,
  `contacts_c_23` varchar(500) DEFAULT NULL,
  `contacts_c_24` varchar(500) DEFAULT NULL,
  `contacts_c_25` varchar(500) DEFAULT NULL,
  `contacts_c_26` varchar(500) DEFAULT NULL,
  `contacts_c_27` varchar(500) DEFAULT NULL,
  `contacts_c_28` varchar(500) DEFAULT NULL,
  `contacts_c_29` varchar(500) DEFAULT NULL,
  `contacts_c_30` varchar(500) DEFAULT NULL,
  `contacts_c_31` varchar(500) DEFAULT NULL,
  `contacts_c_32` varchar(500) DEFAULT NULL,
  `contacts_c_33` varchar(500) DEFAULT NULL,
  `contacts_c_34` varchar(500) DEFAULT NULL,
  `contacts_c_35` varchar(500) DEFAULT NULL,
  `contacts_c_36` varchar(500) DEFAULT NULL,
  `contacts_c_37` varchar(500) DEFAULT NULL,
  `contacts_c_38` varchar(500) DEFAULT NULL,
  `contacts_c_39` varchar(500) DEFAULT NULL,
  `contacts_c_40` varchar(500) DEFAULT NULL,
  `contacts_c_41` varchar(500) DEFAULT NULL,
  `contacts_c_42` varchar(500) DEFAULT NULL,
  `contacts_c_43` varchar(500) DEFAULT NULL,
  `contacts_c_44` varchar(500) DEFAULT NULL,
  `contacts_c_45` varchar(500) DEFAULT NULL,
  `contacts_c_46` varchar(500) DEFAULT NULL,
  `contacts_c_47` varchar(500) DEFAULT NULL,
  `contacts_c_48` varchar(500) DEFAULT NULL,
  `contacts_c_49` varchar(500) DEFAULT NULL,
  `contacts_c_50` varchar(500) DEFAULT NULL,
  `contacts_i_1` varchar(100) DEFAULT NULL,
  `contacts_i_2` varchar(100) DEFAULT NULL,
  `contacts_i_3` varchar(100) DEFAULT NULL,
  `contacts_i_4` varchar(100) DEFAULT NULL,
  `contacts_i_5` varchar(100) DEFAULT NULL,
  `contacts_i_6` varchar(100) DEFAULT NULL,
  `contacts_i_7` varchar(100) DEFAULT NULL,
  `contacts_i_8` varchar(100) DEFAULT NULL,
  `contacts_i_9` varchar(100) DEFAULT NULL,
  `contacts_i_10` varchar(100) DEFAULT NULL,
  `contacts_i_11` varchar(100) DEFAULT NULL,
  `contacts_i_12` varchar(100) DEFAULT NULL,
  `contacts_i_13` varchar(100) DEFAULT NULL,
  `contacts_i_14` varchar(100) DEFAULT NULL,
  `contacts_i_15` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`contact_id`),
  KEY `contact_campaign_id` (`contact_campaign_id`),
  KEY `contact_client_id` (`contact_client_id`),
  KEY `contact_purl2` (`contact_purl2`),
  KEY `contact_purl1` (`contact_purl1`),
  KEY `contact_purl` (`contact_purl`)
)

I have recently Optimized and Defragmented the table as well.

Any ideas on what would be causing this?

Answer 1

First off, thank you for posting your table structure, query, and EXPLAIN output in your question. I think you're crossing the memory / disk temporary table size boundary, thus the large performance change. If you put a unique index on the contact_purl column, MySQL won't allow duplicates to be inserted. This would make your query unnecessary. Otherwise, I'd create an index on (contact_client_id, contact_purl) so MySQL can figure out what rows you want from the indexes directly. You could also try separating the search for the columns and retrieving them by using a subquery. Something like this maybe:

SELECT contact_purl, contact_firstName, contact_lastName, contact_id
FROM contacts, (SELECT MIN(contact_id) AS MinID
FROM contacts
WHERE contact_client_id = 1
GROUP BY contact_purl
HAVING COUNT( contact_id ) > 1) nodups WHERE nodups.MinID = contacts.contact_id