CODEWITHSUNDEEP
mysqlsql
trajan
trajan

Reputation: 1

Efficient use of sub queries?

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;

Upvotes: 0

Views: 256

Answers (2)

zerkms
zerkms

Reputation: 254926

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.

Upvotes: 1

The Scrum Meister
The Scrum Meister

Reputation: 30111

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

Upvotes: 3

Related Questions