CODEWITHSUNDEEP
mysqlperformance
Casper André Casse
Casper André Casse

Reputation: 603

Query becoming slow when updating tables

I have this query

SELECT COUNT( id ) AS count
FROM members_customer_id_1
WHERE id
IN (

SELECT members_in_group.member_id
FROM members_in_group, member_groups
WHERE members_in_group.member_group_id = member_groups.id
AND member_groups.member_group_category_id =3
GROUP BY members_in_group.member_id
)

When making changes to members_in_group, this query gets really, really slow. After first run, everything is smooth until next change in the table.

I suscpected that the problem was with the indexes, so I added an index to members_in_groups.member_id, but this only affected the time when no changes is made to the table.

Could anyone spot why the query can be so slow?

Here is my tables structure:

CREATE TABLE IF NOT EXISTS `members_customer_id_1` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE latin1_danish_ci NOT NULL DEFAULT '',
  `email` varchar(100) COLLATE latin1_danish_ci NOT NULL DEFAULT '',
  `mobile` bigint(16) NOT NULL,
  `zipcode` varchar(10) COLLATE latin1_danish_ci NOT NULL DEFAULT '',
  `sex` varchar(1) COLLATE latin1_danish_ci NOT NULL DEFAULT '',
  `address` varchar(100) COLLATE latin1_danish_ci NOT NULL DEFAULT '',
  `city` varchar(100) COLLATE latin1_danish_ci NOT NULL DEFAULT '',
  `country_id` int(10) NOT NULL,
  `birthday` date NOT NULL DEFAULT '0000-00-00',
  `disable_welcome_message` int(1) NOT NULL,
  `disable_birthday_message` int(1) NOT NULL,
  `confirmed` int(1) NOT NULL,
  `joined` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `deleted` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `origin_type_id` int(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_danish_ci AUTO_INCREMENT=39 ;

CREATE TABLE IF NOT EXISTS `member_groups` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL,
  `name` varchar(100) COLLATE latin1_danish_ci NOT NULL,
  `member_group_category_id` int(11) NOT NULL,
  `hidden` int(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_danish_ci AUTO_INCREMENT=568 ;

CREATE TABLE IF NOT EXISTS `members_in_group` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `member_id` int(10) NOT NULL,
  `member_group_id` int(10) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `member_id` (`member_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_danish_ci AUTO_INCREMENT=421609 ;

Upvotes: 0

Views: 73

Answers (1)

Steven
Steven

Reputation: 1117

I'd try something like this so you can utilize indexes

SELECT COUNT(*) AS count
FROM members_customer_id_1
JOIN members_in_group ON members_customer_id_1.id = members_in_group.member_id
JOIN member_groups ON members_in_group.member_group_id = member_groups.id
WHERE member_groups.member_group_category_id =3

Edit: modified query

Upvotes: 2

Related Questions