CODEWITHSUNDEEP
mysql
brokleyscoding
brokleyscoding

Reputation: 195

Enhance Distinct Query For A Table With Millions Of Records

I have a table in my database that currently holds about 6 million records... And I need to query for distinct users in that table, while also adding filters, but the query is really slow, is there a way to enhance it using mysql, or should I consider other options for big data.

My Table Is Called Interests. It holds, the user_id, the section_id, the property_type_id and the location_id, all of these fields are foreign keys (indexes) that map to "one to many" relationships. And what I want to achieve is something like this.

SELECT COUNT(DISTINCT user_id) 
FROM interests 
WHERE section_id = 1 
  AND property_type_id = 4 AND location_id = 2;

EDIT

Keep in mind that some criteria can be optional for example I could only search for section and location or location and property type etc... Also in future the filters can further expand like we could search for min price and maximum price which could also be optional.

EDIT 2

This is the Create Statement 

CREATE TABLE `user_interests` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) DEFAULT NULL,
`section_id` int(11) DEFAULT NULL,
`location_id` int(11) DEFAULT NULL,
`property_type_id` int(11) DEFAULT NULL,
`created_at` datetime NOT NULL,
`status` smallint(6) DEFAULT NULL,
`min_price` int(11) DEFAULT NULL,
`max_price` int(11) DEFAULT NULL,
`interest_count` int(11) DEFAULT '0',
`locations` longtext COLLATE utf8_unicode_ci,
`deleted_at` datetime DEFAULT NULL,
`auto_saved` tinyint(1) NOT NULL DEFAULT '1',
`keyword` longtext COLLATE utf8_unicode_ci,
PRIMARY KEY (`id`),
KEY `IDX_C854880E64D218E` (`location_id`),
KEY `IDX_C854880E9C81C6EB` (`property_type_id`),
KEY `IDX_C854880EA76ED395` (`user_id`),
KEY `IDX_C854880ED823E37A` (`section_id`),
KEY `IDX_C854880EF066BCDE` (`auto_saved`),
KEY `user_interests_group_idx` (`user_id`,`status`,`created_at`),
CONSTRAINT `FK_C854880E64D218E` FOREIGN KEY (`location_id`) REFERENCES 
`locations` (`id`),
CONSTRAINT `FK_C854880E9C81C6EB` FOREIGN KEY (`property_type_id`) 
REFERENCES `property_types` (`id`),
CONSTRAINT `FK_C854880EA76ED395` FOREIGN KEY (`user_id`) REFERENCES 
`users` (`id`) ON DELETE CASCADE,
CONSTRAINT `FK_C854880ED823E37A` FOREIGN KEY (`section_id`) REFERENCES 
`sections` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6530632 DEFAULT CHARSET=utf8 
COLLATE=utf8_unicode_ci

These are my indexes and their cardinalities

This is the explain statement This is the explain statement

Upvotes: 0

Views: 908

Answers (2)

Wilson Hauck
Wilson Hauck

Reputation: 2343

Consider this multi column index, 

ALTER TABLE user_interests 
ADD INDEX interests_idx_loc_propt_sec_uid (location_id,property_type_id,section_id,user_id) 
;

and reorder the columns in your where list to select highest cardinality first, such as 

SELECT COUNT(DISTINCT user_id) 
FROM user_interests 
WHERE 
location_id = 2
AND 
property_type_id = 4 
AND 
section_id = 1 
;

What does your new EXPLAIN look like and how long does it take to complete?

Upvotes: 0

Barmar
Barmar

Reputation: 781058

Add a multi-column index like:

ALTER TABLE interests ADD INDEX (section_id, property_type_id, user_id);

The first two columns will optimize the WHERE clause, and user_id should optimize COUNT(DISTINCT user_id).

Upvotes: 1

Related Questions