I am trying to handle a scenario that matches other users by their interest and gender.

There will be 2 genders and also 5 interests for each user and each of them will be either 0 or 1. and a rate, the sum (gender+interest) will be calculated and sorted together with last update timestamp in descending order depending on what the user select. (e.g. A user select Female and Movie, ORDER BY SUM(male + movie) DESC, timestamp DESC)

User may select

  1. select interest but not gender
  2. select gender but not interest
  3. both gender and interest
  4. neither gender and interest

Desired outcome:

  1. user A select male and movie
  2. user B select female, no interest (with the most recent timestamp)
  3. user C select female and movie
  4. user D select no gender, and select movie interest
  5. user E select neither

When user A performs the search, the ordered result should be

  1. C with 2 Points
  2. B with 1 Point
  3. D with 1 Point
  4. E with 0 Point

I try to use a table structure like below and use 150,000 rows of data to test it.

CREATE TABLE `candidate_profiles` (
  `user_id` int(11) NOT NULL,
  `male` decimal(3,2) NOT NULL,
  `female` decimal(3,2) NOT NULL,
  `interestA` decimal(3,2) NOT NULL,
  `interestB` decimal(3,2) NOT NULL,
  `interestC` decimal(3,2) NOT NULL,
  `interestD` decimal(3,2) NOT NULL,
  `interestE` decimal(3,2) NOT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  PRIMARY KEY (`id`),

Below is the query that I used

    p.id AS profile_id,
    (gender + interestB) AS point,
        WHEN male = 1 THEN 'male'
        WHEN female = 1 THEN 'female'
        ELSE 'disabled'
    END AS 'gender',
        WHEN interestA = 1 THEN 'interestA'
        WHEN interestB = 1 THEN 'interestB'
        WHEN interestC= 1 THEN 'interestC'
        WHEN interestD = 1 THEN 'interestD'
        WHEN interestE = 1 THEN 'interestE'
        ELSE 'disabled'
    END AS 'interest'
    candidate c
        INNER JOIN
    profiles p ON p.candidate_id = c.id
        LEFT JOIN
    ignored ci ON ci.candidate_id = 1
        AND ci.target_id = c.id
    c.state = 0 
    AND ci.id IS NULL
    AND c.id NOT IN (151011 , 151009,
ORDER BY c.timestamp DESC , point DESC

and this is the EXPLAIN result

| id | select_type | table | partitions | type   | possible_keys                                                                                                                    | key                                       | key_len | ref             | rows  | filtered | Extra                                                  |
|  1 | SIMPLE      | c     | NULL       | ref    | PRIMARY,idx_distributor_candidate_timestamp,idx_distributor_candidate_state_timestamp,v110_id_timestamp_state,id_timestamp_state | idx_distributor_candidate_state_timestamp | 4       | const           | 75551 |    50.01 | Using index condition; Using temporary; Using filesort |
|  1 | SIMPLE      | ci    | NULL       | eq_ref | uk_candidate_id_target_id                                                                                                        | uk_candidate_id_target_id                 | 8       | const,wowo.c.id |     1 |    33.33 | Using where; Not exists; Using index                   |
|  1 | SIMPLE      | p     | NULL       | ref    | idx_candidate_profile_2_candidate_id                                                                                             | idx_candidate_profile_2_candidate_id      | 4       | wowo.c.id       |     1 |   100.00 | NULL                                                   |

I realize, because of the sum operation, I can't create a proper index. And the query used 3-4 seconds.

I want to seek another way that could speed up the query. Would "Pre-Calculated" values be the way? meaning with 2 genders and 5 interests, it would have 17 columns of variations.

Or is there another approach that could be used. thanks!

Updated, Oct 31: Add the desired outcome. Current query and EXPLAIN result

  • "disabled", as in gelding?
    – Rick James
    Oct 31, 2018 at 4:49
  • Is the CASE expression saying that a user can have at most 1 interest? Or is it saying that A is more important than B, etc.?
    – Rick James
    Oct 31, 2018 at 4:51
  • @Rick James A user can have at most 1 interest. There was a typo, "point" should be (gender + interest). Disabled just a value indicates user did not select any interest or gender. Nov 1, 2018 at 3:50
  • Sounds like ENUMs (one for interest, one for gender) would be 'best'.
    – Rick James
    Nov 1, 2018 at 4:06

1 Answer 1


If all you have is 0 or 1, then TINYINT UNSIGNED should suffice (and be half the size).

Are you selecting all the 'matching' users? Or just 'counting' how many there are?

If selecting all that match, I would consider using a TINYINT UNSIGNED because it has 8 bits and they could represent gender, the interests, and is_active. Then apply a mask to select the bits that need to be looked at, and an '=' to test them all at one time.

I see no need for the interests to be in separate column. Instead, have a single column with

interests ENUM ('a', 'b', ...)
INDEX(interests, gender)
INDEX(gender, interests)

WHERE gender = 1 AND interests = 'a'
WHERE interests = 'd'
(etc -- covering the 4 cases you mentioned)

Please provide complete queries, and/or sample output.

  • Thanks for the advice, I will definitely go for TINYINT UNSIGNED. I just updated the question with a desired outcome of the query. I use separated columns for different interests because users that are not the exact match may also be considered. Oct 31, 2018 at 2:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.