Menu
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
238
In this case, 1 of the tables was MyISAM and the other was InnoDB
When I switched both to InnoDB it magically changed from ALL to ref and from scanning all rows to a subset.
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 | 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.00 sec)
mysql> ALTER TABLE `members` ENGINE = InnoDB;
Query OK, 140245 rows affected (1 min 8.10 sec)
Records: 140245 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 | ref | A | A | 1 | const | 53916 | Using where |
| 1 | SIMPLE | MyFriend | ref | member_1_id | member_1_id | 4 | const | 181 | Using where; Using index |
+----+-------------+----------+------+---------------+-------------+---------+-------+-------+--------------------------+
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With