I've tried this a few different ways and am getting bad results.

The Core problem is that Member Search is scanning ALL members, ignoring indexes.

The main reason (from what I can tell) is this fragment 

`(Member.priv_profile = 3 OR MyFriend.status_id IN (1,2))`

Either side of that OR fragment alone, works fine, getting an index, scanning a few rows, and thus performing well.

I really don't want to split this query into 2 and do a UNION but we might have to do so unless someone can come up with a good way making this select "work" with the important OR.

```
mysql> ALTER TABLE `members` ADD INDEX A (is_active, last_name, first_name);
Query OK, 140019 rows affected (6.82 sec)
Records: 140019  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `members` ADD INDEX B (is_active, last_name, first_name, priv_profile);
Query OK, 140019 rows affected (7.70 sec)
Records: 140019  Duplicates: 0  Warnings: 0

mysql> explain SELECT COUNT(*) AS `count` FROM `ao_prod`.`members` AS `Member` 
LEFT JOIN `ao_prod`.`member_friends` AS `MyFriend` ON (`MyFriend`.`member_2_id` = `Member`.`id` AND member_1_id = '150365')  
WHERE `Member`.`is_active` = '1' AND NOT(`Member`.`first_name` = '' AND `Member`.`last_name` = '') AND (`Member`.`priv_profile` = 3 OR `MyFriend`.`status_id` IN (1,2));
+----+-------------+----------+------+----------------------------------------------+-------------+---------+-------+--------+--------------------------+
| id | select_type | table    | type | possible_keys                                | key         | key_len | ref   | rows   | Extra                    |
+----+-------------+----------+------+----------------------------------------------+-------------+---------+-------+--------+--------------------------+
|  1 | SIMPLE      | Member   | ALL  | active_delete,scope,member_search_alerts,A,B | NULL        | NULL    | NULL  | 140019 | Using where              |
|  1 | SIMPLE      | MyFriend | ref  | member_1_id                                  | member_1_id | 4       | const |    155 | Using where; Using index |
+----+-------------+----------+------+----------------------------------------------+-------------+---------+-------+--------+--------------------------+
2 rows in set (0.00 sec)

// without the "public profile" part

mysql> explain SELECT COUNT(*) AS `count` FROM `ao_prod`.`members` AS `Member` 
LEFT JOIN `ao_prod`.`member_friends` AS `MyFriend` ON (`MyFriend`.`member_2_id` = `Member`.`id` AND member_1_id = '150365')  
WHERE `Member`.`is_active` = '1' AND NOT(`Member`.`first_name` = '' AND `Member`.`last_name` = '') AND (`MyFriend`.`status_id` IN (1,2));
+----+-------------+----------+--------+------------------------------------------------------+-------------+---------+------------------------------+------+--------------------------+
| id | select_type | table    | type   | possible_keys                                        | key         | key_len | ref                          | rows | Extra                    |
+----+-------------+----------+--------+------------------------------------------------------+-------------+---------+------------------------------+------+--------------------------+
|  1 | SIMPLE      | MyFriend | range  | member_1_id                                          | member_1_id | 5       | NULL                         |  251 | Using where; Using index |
|  1 | SIMPLE      | Member   | eq_ref | PRIMARY,active_delete,scope,member_search_alerts,A,B | PRIMARY     | 4       | ao_prod.MyFriend.member_2_id |    1 | Using where              |
+----+-------------+----------+--------+------------------------------------------------------+-------------+---------+------------------------------+------+--------------------------+
2 rows in set (0.00 sec)

// without the "my connection" part

mysql> explain SELECT COUNT(*) AS `count` FROM `ao_prod`.`members` AS `Member` 
LEFT JOIN `ao_prod`.`member_friends` AS `MyFriend` ON (`MyFriend`.`member_2_id` = `Member`.`id` AND member_1_id = '42983')  
WHERE `Member`.`is_active` = '1' AND ( NOT(`Member`.`first_name` = '' AND `Member`.`last_name` = '')) AND (`Member`.`priv_profile` = 3);
+----+-------------+----------+------+----------------------------------------------+-------------+---------+-------------+------+-------------+
| id | select_type | table    | type | possible_keys                                | key         | key_len | ref         | rows | Extra       |
+----+-------------+----------+------+----------------------------------------------+-------------+---------+-------------+------+-------------+
|  1 | SIMPLE      | Member   | ref  | active_delete,scope,member_search_alerts,A,B | scope       | 2       | const,const | 2007 | Using where |
|  1 | SIMPLE      | MyFriend | ref  | member_1_id                                  | member_1_id | 4       | const       |  252 | Using index |
+----+-------------+----------+------+----------------------------------------------+-------------+---------+-------------+------+-------------+
2 rows in set (0.01 sec)

// as a subquery vs. join (no workie)

mysql> explain SELECT COUNT(*) AS `count` FROM `ao_prod`.`members` AS `Member` 
WHERE `Member`.`is_active` = '1' AND NOT(`Member`.`first_name` = '' AND `Member`.`last_name` = '') AND ( `Member`.`id` IN (
 SELECT member_2_id FROM member_friends WHERE member_1_id = 150365 AND status_id IN (1,2)  
));
+----+--------------------+----------------+-------+----------------------------------------------+-------------+---------+------+--------+--------------------------+
| id | select_type        | table          | type  | possible_keys                                | key         | key_len | ref  | rows   | Extra                    |
+----+--------------------+----------------+-------+----------------------------------------------+-------------+---------+------+--------+--------------------------+
|  1 | PRIMARY            | Member         | ALL   | active_delete,scope,member_search_alerts,A,B | NULL        | NULL    | NULL | 140019 | Using where              |
|  2 | DEPENDENT SUBQUERY | member_friends | range | member_1_id                                  | member_1_id | 5       | NULL |    155 | Using where; Using index |
+----+--------------------+----------------+-------+----------------------------------------------+-------------+---------+------+--------+--------------------------+
2 rows in set (0.01 sec)

// sketch of the possible, ugly UNION

mysql> explain SELECT COUNT(*) AS `count` FROM `ao_prod`.`members` AS `Member` LEFT JOIN `ao_prod`.`member_friends` AS `MyFriend` ON (`MyFriend`.`member_2_id` = `Member`.`id` AND member_1_id = '42983')  WHERE `Member`.`is_active` = '1' AND ( NOT(`Member`.`first_name` = '' AND `Member`.`last_name` = '')) AND (`MyFriend`.`status_id` IN (1,2))
    -> UNION
    -> SELECT COUNT(*) AS `count` FROM `ao_prod`.`members` AS `Member`  WHERE `Member`.`is_active` = '1' AND ( NOT(`Member`.`first_name` = '' AND `Member`.`last_name` = '')) AND (`Member`.`priv_profile` = 3)
    -> GROUP BY Member.id
    -> ;
+----+--------------+------------+--------+------------------------------------------------------+-------------+---------+------------------------------+------+----------------------------------------------+
| id | select_type  | table      | type   | possible_keys                                        | key         | key_len | ref                          | rows | Extra                                        |
+----+--------------+------------+--------+------------------------------------------------------+-------------+---------+------------------------------+------+----------------------------------------------+
|  1 | PRIMARY      | MyFriend   | range  | member_1_id                                          | member_1_id | 5       | NULL                         |  251 | Using where; Using index                     |
|  1 | PRIMARY      | Member     | eq_ref | PRIMARY,active_delete,scope,member_search_alerts,A,B | PRIMARY     | 4       | ao_prod.MyFriend.member_2_id |    1 | Using where                                  |
|  2 | UNION        | Member     | ref    | active_delete,scope,member_search_alerts,A,B         | scope       | 2       | const,const                  | 2007 | Using where; Using temporary; Using filesort |
| NULL | UNION RESULT | <union1,2> | ALL    | NULL                                                 | NULL        | NULL    | NULL                         | NULL |                                              |
+----+--------------+------------+--------+------------------------------------------------------+-------------+---------+------------------------------+------+----------------------------------------------+
4 rows in set (0.02 sec)

// using index hinting to no avail

mysql> explain SELECT COUNT(*) AS `count` 
FROM `ao_prod`.`members` AS `Member` 
USE INDEX (A)  
LEFT JOIN `ao_prod`.`member_friends` AS `MyFriend` ON (`MyFriend`.`member_2_id` = `Member`.`id` AND member_1_id = '150365')  
WHERE `Member`.`is_active` = '1' AND NOT(`Member`.`first_name` = '' AND `Member`.`last_name` = '') AND (`Member`.`priv_profile` = 3 OR `MyFriend`.`status_id` IN (1,2));
+----+-------------+----------+------+---------------+-------------+---------+-------+--------+--------------------------+
| id | select_type | table    | type | possible_keys | key         | key_len | ref   | rows   | Extra                    |
+----+-------------+----------+------+---------------+-------------+---------+-------+--------+--------------------------+
|  1 | SIMPLE      | Member   | ALL  | A             | NULL        | NULL    | NULL  | 140245 | Using where              |
|  1 | SIMPLE      | MyFriend | ref  | member_1_id   | member_1_id | 4       | const |    181 | Using where; Using index |
+----+-------------+----------+------+---------------+-------------+---------+-------+--------+--------------------------+
2 rows in set (0.01 sec)

```

Here are create statements for the involved tables (full, ugly tables and all other indexes shown)

```
CREATE TABLE IF NOT EXISTS `member_friends` (
  `id` varchar(36) NOT NULL,
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  `member_1_id` int(11) NOT NULL DEFAULT '0',
  `member_2_id` int(11) NOT NULL DEFAULT '0',
  `status_id` tinyint(3) NOT NULL DEFAULT '0',
  `requested_by` tinyint(3) NOT NULL DEFAULT '0',
  `requested` datetime DEFAULT NULL,
  `accepted` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `member_1_id` (`member_1_id`,`status_id`,`member_2_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `members_fields`
 ADD PRIMARY KEY (`id`), ADD KEY `key` (`key`), ADD KEY `member_key` (`member_id`,`key`);

CREATE TABLE IF NOT EXISTS `members` (
`id` int(11) NOT NULL,
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  `profile_updated` datetime NOT NULL,
  `last_login` datetime DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL,
  `email` varchar(256) NOT NULL DEFAULT '',
  `password` varchar(40) NOT NULL,
  `first_name` varchar(128) NOT NULL DEFAULT '',
  `middle_name` varchar(128) NOT NULL,
  `last_name` varchar(128) NOT NULL DEFAULT '',
  `suffix` varchar(32) NOT NULL,
  `company` varchar(128) NOT NULL,
  `address` varchar(128) NOT NULL,
  `address_2` varchar(128) NOT NULL,
  `city` varchar(128) NOT NULL,
  `state` varchar(5) NOT NULL,
  `zip` varchar(16) NOT NULL,
  `location_name` varchar(128) NOT NULL,
  `image_url` varchar(256) NOT NULL,
  `slug` varchar(64) NOT NULL,
  `headline` varchar(256) NOT NULL,
  `experience_level` varchar(64) NOT NULL,
  `apply_job_states` varchar(256) NOT NULL COMMENT 'CSV list',
  `apply_job_us` tinyint(1) NOT NULL DEFAULT '0',
  `apply_job_ca` tinyint(1) NOT NULL DEFAULT '0',
  `apply_job_traveling` tinyint(1) NOT NULL DEFAULT '0',
  `apply_job_international` tinyint(1) NOT NULL DEFAULT '0',
  `apply_job_fulltime` tinyint(1) NOT NULL DEFAULT '0',
  `apply_job_parttime` tinyint(1) NOT NULL DEFAULT '0',
  `apply_job_perdiem` tinyint(1) NOT NULL DEFAULT '0',
  `contact_for_professional_opportunities` tinyint(1) NOT NULL DEFAULT '0',
  `contact_for_job_inquiries` tinyint(1) NOT NULL DEFAULT '0',
  `contact_for_new_ventures` tinyint(1) NOT NULL DEFAULT '0',
  `contact_for_expertise_requests` tinyint(1) NOT NULL DEFAULT '0',
  `country` varchar(2) NOT NULL,
  `timezone` varchar(32) NOT NULL,
  `phone` varchar(16) NOT NULL,
  `fax` varchar(16) NOT NULL,
  `birthday` varchar(5) NOT NULL COMMENT 'MM/DD (required)',
  `birth_year` varchar(4) DEFAULT NULL COMMENT 'YYYY (optional)',
  `corp_id` int(11) NOT NULL DEFAULT '0',
  `is_deleted` tinyint(1) NOT NULL,
  `url` varchar(256) DEFAULT NULL,
  `emails` varchar(512) NOT NULL COMMENT 'JSON list of alternate emails',
  `phones` varchar(512) NOT NULL COMMENT 'JSON list of alternate phones',
  `lat` float NOT NULL,
  `lon` float NOT NULL,
  `facebook_id` varchar(32) NOT NULL,
  `connect_id` int(11) NOT NULL,
  `is_student` tinyint(1) NOT NULL DEFAULT '0',
  `is_career_center_recruiter` tinyint(1) NOT NULL DEFAULT '0',
  `is_continuing_education_portal_manager` tinyint(1) NOT NULL DEFAULT '0',
  `is_manually_approved` tinyint(1) NOT NULL DEFAULT '0',
  `is_employer` tinyint(1) NOT NULL DEFAULT '0',
  `is_jobseeker` tinyint(1) NOT NULL DEFAULT '0',
  `is_jobseeker_badge` tinyint(1) NOT NULL DEFAULT '0',
  `is_contributor` tinyint(1) NOT NULL DEFAULT '0',
  `priv_profile` tinyint(3) NOT NULL DEFAULT '1',
  `priv_email` tinyint(3) NOT NULL DEFAULT '0',
  `priv_phone` tinyint(3) NOT NULL DEFAULT '0',
  `has_certification` tinyint(1) DEFAULT NULL,
  `has_state_license` tinyint(1) DEFAULT NULL,
  `job_title` varchar(64) NOT NULL,
  `occupation_id` int(11) NOT NULL,
  `occupation_other` varchar(64) NOT NULL,
  `work_setting_id` int(11) NOT NULL,
  `work_setting_other` varchar(64) NOT NULL,
  `memberships_honors_awards` text NOT NULL
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1093688 ;

ALTER TABLE `members`
 ADD PRIMARY KEY (`id`), ADD KEY `is_cc` (`is_career_center_recruiter`,`corp_id`), ADD KEY `is_ce` (`is_continuing_education_portal_manager`,`corp_id`), ADD KEY `corp_id` (`corp_id`), ADD KEY `active_delete` (`is_active`,`is_deleted`), ADD KEY `delete` (`is_deleted`), ADD KEY `email_pass` (`email`,`password`), ADD KEY `apply_job_states` (`apply_job_states`,`apply_job_us`,`apply_job_ca`), ADD KEY `experience_level` (`experience_level`), ADD KEY `latlon` (`lat`,`lon`), ADD KEY `location` (`state`,`zip`), ADD KEY `slug` (`slug`,`is_active`,`priv_profile`), ADD KEY `scope` (`is_active`,`priv_profile`,`state`), ADD KEY `member_search_alerts` (`is_active`,`is_jobseeker`,`profile_updated`,`priv_profile`,`apply_job_us`,`apply_job_ca`);

```

UPDATE: as requested, here are the optimizer settings

```
mysql> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on
1 row in set (0.00 sec)

```

NOTE: this has been tested on

* Server version: 5.6.20-68.0-56-log - Percona XtraDB Cluster (GPL), Release 25.7
* Server version: 5.5.29-0ubuntu0.12.04.1
* Server version: 5.1.72 - Source distribution