Re: Slow search on one table, many where, order by and limit

[Rick James]

| key_buffer_size | 26214400 |
Index_length: 69526528

How much RAM? Assuming you have at least 1GB, recommend
key_buffer_size = 100M
so that all the indexes for that table have a chance of staying in RAM.

Are you really SELECTing CARD_ID? Or are you selecting a lot of other columns? If just CARD_ID, then I recommend

  INDEX(GENDER, COUNTRY_ID, LOOKING_FOR_GENDERS, CARD_FLAGS, CARD_ID)

That way, the SELECTs that you showed can be run "Using index". That tends to be somewhat faster than having to run off to the data for each row.

Consider adding "unknown" to the ENUMs and making them NOT NULL. I don't know how much this will help; perhaps not enough to be worth doing.

Some reason for height not being a TINYINT UNSIGNED?

A bunch of the INTs could be SMALLINT or TINYINT. (Ok, your table is only 41MB, so this won't really help any.)

Recommend you turn on the slowlog, and set long_query_time = 2, then we can discuss the naughties that show up in the slowlog.