Efficient use of sub queries?

Question

I was wondering if any one could point out potential problems with the following query or if there was a better alternative

From a list of users I want to return all who don't have all the specified user_profile options or those who do not have at least one preference set to 1. The following query seems to work fine.

SELECT DISTINCT(c.id) FROM user c
                 WHERE c.id IN (901,907)
                 AND 
        ((SELECT COUNT(id) FROM user_profile
                          WHERE option_id in (747,749) AND user_id=c.id) < 2
         OR
        (SELECT COUNT(id) FROM user_profile
                          WHERE option_id in (747,749) AND user_id=c.id AND preference = 1) != 1
         )

The table definition

CREATE TABLE `user_profile` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `option_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `preference` decimal(10,2) DEFAULT '0.00',
  `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_profile_user_id` (`user_id`),
  KEY `user_profile_option_id` (`option_id`),
  CONSTRAINT `user_profile_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`),
  CONSTRAINT `user_profile_option_id` FOREIGN KEY (`option_id`) REFERENCES `option` (`id`),
) ENGINE=InnoDB;

related: http://stackoverflow.com/questions/560777/sql-query-performance-question-multiple-sub-queries — Lightness Races in Orbit, Jul 14 '11 at 23:17

score 3 · Answer 1 · answered Jul 14 '11 at 23:20

3

Join the 2 predicates into a single query, which will perform a single table lookup.

SELECT c.id
FROM user c LEFT JOIN user_profile p 
     ON c.id = p.user_id
     AND p.option_id in (747, 749)
WHERE user_id IN (901,907)
GROUP BY c.id
HAVING COUNT(p.id) < 2 OR SUM(p.preference = 1) != 1

answered Jul 14 '11 at 23:20

The Scrum Meister

29,681
8
66
64

His query returns user's ids, while yours returns just their count. +1 anyway – zerkms Jul 14 '11 at 23:21

score 1 · Answer 2 · answered Jul 14 '11 at 23:20

1

As long as you have PK-based condition c.id IN (901,907) - it will not be a problem.

Because mysql will filter by id at first and after that it will perform nested queries for 2 records maximum.

answered Jul 14 '11 at 23:20

zerkms

249,484
69
436
539

Efficient use of sub queries?

2 Answers2